My Collection


Table Of Contents
Chapter 1
String Functions (Transact-SQL)
Mathematical Functions (Transact-SQL)
Logical Functions (Transact-SQL)
Data Type Functions (Transact-SQL)
Conversion Functions (Transact-SQL)
Aggregate Functions (Transact-SQL)
Date and Time Data Types and Functions (Transact-SQL)


Chapter 1
Export (0) Print
Expand All

String Functions (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

The following scalar functions perform an operation on a string input value and return a string or numeric value:

ASCII           

LTRIM           

SOUNDEX           

CHAR           

NCHAR           

SPACE           

CHARINDEX           

PATINDEX           

STR           

CONCAT

QUOTENAME           

STUFF           

DIFFERENCE           

REPLACE           

SUBSTRING           

FORMAT

REPLICATE           

UNICODE           

LEFT           

REVERSE           

UPPER           

LEN           

RIGHT           

 

LOWER           

RTRIM           

 

All built-in string functions are deterministic. This means they return the same value any time they are called with a specific set of input values. For more information about function determinism, see Deterministic and Nondeterministic Functions.

When string functions are passed arguments that are not string values, the input type is implicitly converted to a text data type. For more information, see Data Type Conversion (Database Engine).

© 2015 Microsoft
Export (0) Print
Expand All

ASCII (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the ASCII code value of the leftmost character of a character expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ASCII ( character_expression )

character_expression

Is an expression of the type char or varchar.

The following example assumes an ASCII character set and returns the ASCII value and CHAR character for each character in the string Du monde entier.

SET TEXTSIZE 0;
SET NOCOUNT ON;
-- Create the variables for the current character string position 
-- and for the character string.
DECLARE @position int, @string char(15);
-- Initialize the variables.
SET @position = 1;
SET @string = 'Du monde entier';
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END;
SET NOCOUNT OFF;
GO

Here is the result set.

----------- - 
68          D 
              
----------- - 
117         u 
              
----------- - 
32            
              
----------- - 
109         m 
              
----------- - 
111         o 
              
----------- - 
110         n 
              
----------- - 
100         d 
              
----------- - 
101         e 
              
----------- - 
32            
              
----------- - 
101         e 
              
----------- - 
110         n 
              
----------- - 
116         t 
              
----------- - 
105         i 
              
----------- - 
101         e 
              
----------- - 
114         r
© 2015 Microsoft
Export (0) Print
Expand All

CHAR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Converts an int ASCII code to a character.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

CHAR ( integer_expression )

integer_expression

Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

CHAR can be used to insert control characters into character strings. The following table shows some frequently used control characters.

Control character

Value

Tab

char(9)

Line feed

char(10)

Carriage return

char(13)

The following example prints the ASCII value and character for each character in the string New Moon.

SET TEXTSIZE 0;
-- Create variables for the character string and for the current 
-- position in the string.
DECLARE @position int, @string char(8);
-- Initialize the current position and the string variables.
SET @position = 1;
SET @string = 'New Moon';
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)), 
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
   SET @position = @position + 1
   END;
GO

Here is the result set.

----------- -

78 N

              

----------- -

101 e

              

----------- -

119 w

              

----------- -

32

              

----------- -

77 M

              

----------- -

111 o

              

----------- -

111 o

              

----------- -

110 n

              

----------- -

The following example uses CHAR(13) to print the name and e-mail address of an employee on separate lines when the results are returned in text. This example uses the AdventureWorks2012 database.

SELECT p.FirstName + ' ' + p.LastName, + CHAR(13)  + pe.EmailAddress 
FROM Person.Person p JOIN Person.EmailAddress pe
ON p.BusinessEntityID = pe.BusinessEntityID
AND p.BusinessEntityID = 1;
GO

Here is the result set.

Ken Sanchez

ken0@adventure-works.com

(1 row(s) affected)

© 2015 Microsoft
Export (0) Print
Expand All

CHARINDEX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Searches an expression for another expression and returns its starting position if found.

Topic link icon Transact-SQL Syntax Conventions

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) 

expressionToFind

Is a character expression that contains the sequence to be found. expressionToFind is limited to 8000 characters.

expressionToSearch

Is a character expression to be searched.

start_location

Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.

bigint if expressionToSearch is of the varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, int.

If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.

If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.

If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

The starting position returned is 1-based, not 0-based.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

When using SC collations, both start_location and the return value count surrogate pairs as one character, not two. For more information, see Collation and Unicode Support.

The following example returns the position at which the sequence of characters bicycle starts in the DocumentSummary column of the Document table in the AdventureWorks2012 database.

DECLARE @document varchar(64);
SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bicycle', @document);
GO

Here is the result set.

----------- 
48          

The following example uses the optional start_location parameter to start looking for vital at the fifth character of the DocumentSummary column in the AdventureWorks2012 database.

DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('vital', @document, 5);
GO

Here is the result set.

----------- 
16          

(1 row(s) affected)

The following example shows the result set when expressionToFind is not found within expressionToSearch.

DECLARE @document varchar(64);

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.';
SELECT CHARINDEX('bike', @document);
GO

Here is the result set.

-----------

0

(1 row(s) affected)

The following example performs a case-sensitive search for the string 'TEST' in 'This is a Test'.

USE tempdb;
GO
--perform a case sensitive search
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Here is the result set.

-----------

0

The following example performs a case-sensitive search for the string 'Test' in 'Das ist ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'Test',
       'This is a Test'
       COLLATE Latin1_General_CS_AS);

Here is the result set.

-----------

13

The following example performs a case-insensitive search for the string 'TEST' in 'Das ist ein Test'.

USE tempdb;
GO
SELECT CHARINDEX ( 'TEST',
       'This is a Test'
       COLLATE Latin1_General_CI_AS);
GO

Here is the result set.

-----------

13

© 2015 Microsoft
Export (0) Print
Expand All

CONCAT (Transact-SQL)

 

Updated: November 20, 2015

Applies To: Azure SQL Database, SQL Server (starting with 2008)

Returns a string that is the result of concatenating two or more string values.

Topic link icon Transact-SQL Syntax Conventions

CONCAT ( string_value1, string_value2 [, string_valueN ] )

string_value

A string value to concatenate to the other values.

String, the length and type of which depend on the input.

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

The return type depends on the type of the arguments. The following table illustrates the mapping.

Input type

Output type and length

If any argument is a SQL-CLR system type, a SQL-CLR UDT, or nvarchar(max)

nvarchar(max)

Otherwise, if any argument is varbinary(max) or varchar(max)

varchar(max) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).

Otherwise, if any argument is nvarchar(<= 4000)

nvarchar(<= 4000)

Otherwise, in all other cases

varchar(<= 8000) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).

When the arguments are <= 4000 for nvarchar, or <= 8000 for varchar, implicit conversions can affect the length of the result. Other data types have different lengths when they are implicitly converted to strings. For example, an int (14) has a string length of 12, while a float has a length of 32. Thus the result of concatenating two integers has a length of no less than 24.

If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

The CONCAT function can be executed remotely on a linked server which is version SQL Server 2012 and above. For older linked servers, the CONCAT operation will be performed locally after the non-concatenated values are returned from the linked server.

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

Here is the result set.

Result
-------------------------
Happy Birthday 11/25

(1 row(s) affected)

CREATE TABLE #temp (
    emp_name nvarchar(200) NOT NULL,
    emp_middlename nvarchar(200) NULL,
    emp_lastname nvarchar(200) NOT NULL
);
INSERT INTO #temp VALUES( 'Name', NULL, 'Lastname' );
SELECT CONCAT( emp_name, emp_middlename, emp_lastname ) AS Result
FROM #temp;

Here is the result set.

Result
------------------
NameLastname

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DIFFERENCE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DIFFERENCE ( character_expression , character_expression )

character_expression

Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

DIFFERENCE and SOUNDEX are collation sensitive.

In the first part of the following example, the SOUNDEX values of two very similar strings are compared. For a Latin1_General collation DIFFERENCE returns a value of 4. In the second part of the following example, the SOUNDEX values for two very different strings are compared, and for a Latin1_General collation DIFFERENCE returns a value of 0.

-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'), SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene');
GO

Here is the result set.

----- ----- ----------- 
G650  G650  4           

(1 row(s) affected)
                        
----- ----- ----------- 
B432  G650  0           

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

FORMAT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

FORMAT ( value, format [, culture ] )

value

Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.

format

nvarchar format pattern.

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)"). Composite formatting is not supported. For a full explanation of these formatting patterns, please consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. A good starting point is the topic, "Formatting Types."

culture

Optional nvarchar argument specifying a culture.

If the culture argument is not provided, the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.

nvarchar or null

The length of the return value is determined by the format.

FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

Category

Type

.NET type

Numeric

bigint

Int64

Numeric

int

Int32

Numeric

smallint

Int16

Numeric

tinyint

Byte

Numeric

decimal

SqlDecimal

Numeric

numeric

SqlDecimal

Numeric

float

Double

Numeric

real

Single

Numeric

smallmoney

Decimal

Numeric

money

Decimal

Date and Time

date

DateTime

Date and Time

time

TimeSpan

Date and Time

datetime

DateTime

Date and Time

smalldatetime

DateTime

Date and Time

datetime2

DateTime

Date and Time

datetimeoffset

DateTimeOffset

The following example returns a simple date formatted for different cultures.

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; 
  
SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result'; 

Here is the result set.

US English Result Great Britain English Result  German Result Simplified Chinese (PRC) Result
----------------  ----------------------------- ------------- -------------------------------------
10/1/2011         01/10/2011                    01.10.2011    2011/10/1

(1 row(s) affected)

US English Result            Great Britain English Result  German Result                    Chinese (Simplified PRC) Result
---------------------------- ----------------------------- -----------------------------  ---------------------------------------
Saturday, October 01, 2011   01 October 2011               Samstag, 1. Oktober 2011        2011年10月1日

(1 row(s) affected)


The following example shows formatting numeric values by specifying a custom format. For more information about these and other custom formats, see Custom Numeric Format Strings.

-- Current date is September 27 2012.
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'
       ,FORMAT(123456789,'###-##-####') AS 'Custom Number Result';

Here is the result set.

DateTime Result  Custom Number Result
--------------   --------------------
27/09/2012       123-45-6789

(1 row(s) affected)


The following example returns 5 rows from the Sales.CurrencyRate table in the AdventureWorks2012 database. The column EndOfDateRate is stored as type money in the table. In this example, the column is returned unformatted and then formatted by specifying the .NET Number format, General format, and Currency format types. For more information about these and other numeric formats, see Standard Numeric Format Strings.

bSELECT TOP(5)CurrencyRateID, EndOfDayRate
            ,FORMAT(EndOfDayRate, 'N', 'en-us') AS 'Number Format'
            ,FORMAT(EndOfDayRate, 'G', 'en-us') AS 'General Format'
            ,FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

Here is the result set.

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format
-------------- ------------  --------------  --------------  ---------------
1              1.0002        1.00            1.0002          $1.00
2              1.55          1.55            1.5500          $1.55
3              1.9419        1.94            1.9419          $1.94
4              1.4683        1.47            1.4683          $1.47
5              8.2784        8.28            8.2784          $8.28

(5 row(s) affected)


This example specifies the German culture (de-de).

SELECT TOP(5)CurrencyRateID, EndOfDayRate
      ,FORMAT(EndOfDayRate, 'N', 'de-de') AS 'Numeric Format'
      ,FORMAT(EndOfDayRate, 'G', 'de-de') AS 'General Format'
      ,FORMAT(EndOfDayRate, 'C', 'de-de') AS 'Currency Format'
FROM Sales.CurrencyRate
ORDER BY CurrencyRateID;

CurrencyRateID EndOfDayRate  Numeric Format  General Format  Currency Format
-------------- ------------  --------------  --------------  ---------------
1              1.0002        1,00            1,0002          1,00 €
2              1.55          1,55            1,5500          1,55 €
3              1.9419        1,94            1,9419          1,94 €
4              1.4683        1,47            1,4683          1,47 €
5              8.2784        8,28            8,2784          8,28 €

 (5 row(s) affected)


© 2015 Microsoft
Export (0) Print
Expand All

LEFT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the left part of a character string with the specified number of characters.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LEFT ( character_expression , integer_expression )

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

The integer_expression parameter counts a UTF-16 surrogate character as one character.

Returns varchar when character_expression is a non-Unicode character data type.

Returns nvarchar when character_expression is a Unicode character data type.

When using SC collations, the integer_expression parameter counts a UTF-16 surrogate pair as one character. For more information, see Collation and Unicode Support.

The following example returns the five leftmost characters of each product name in the Product table of the AdventureWorks2012 database.

SELECT LEFT(Name, 5) 
FROM Production.Product
ORDER BY ProductID;
GO

The following example uses LEFT to return the two leftmost characters of the character string abcdefg.

SELECT LEFT('abcdefg',2);
GO

Here is the result set.

-- 
ab 

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

LEN (Transact-SQL)

 

Updated: September 3, 2015

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the number of characters of the specified string expression, excluding trailing blanks.

System_CAPS_noteNote

To return the number of bytes used to represent an expression, use the DATALENGTH function.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LEN ( string_expression )

string_expression

Is the string expression to be evaluated. string_expression can be a constant, variable, or column of either character or binary data.

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

If you are using SC collations, the returned integer value counts UTF-16 surrogate pairs as a single character. For more information, see Collation and Unicode Support.

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters. The following example demonstrates LEN and DATALENGTH with a trailing space.

DECLARE @v1 varchar(40),
    @v2 nvarchar(40);
SELECT 
@v1 = 'Test of 22 characters ', 
@v2 = 'Test of 22 characters ';
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];
SELECT LEN(@v2) AS [nvarchar LEN], DATALENGTH(@v2) AS [nvarchar DATALENGTH];

The following example selects the number of characters and the data in FirstName for people located in Australia. This example uses the AdventureWorks2012 database.

SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO
© 2015 Microsoft
Export (0) Print
Expand All

LOWER (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a character expression after converting uppercase character data to lowercase.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LOWER ( character_expression )

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example uses the LOWER function, the UPPER function, and nests the UPPER function inside the LOWER function in selecting product names that have prices between $11 and $20. This example uses the AdventureWorks2012 database.

SELECT LOWER(SUBSTRING(Name, 1, 20)) AS Lower, 
   UPPER(SUBSTRING(Name, 1, 20)) AS Upper, 
   LOWER(UPPER(SUBSTRING(Name, 1, 20))) As LowerUpper
FROM Production.Product
WHERE ListPrice between 11.00 and 20.00;
GO

Here is the result set.

Lower Upper LowerUpper

--------------------- --------------------- --------------------

minipump MINIPUMP minipump

taillights - battery TAILLIGHTS - BATTERY taillights - battery

(2 row(s) affected)

© 2015 Microsoft
Export (0) Print
Expand All

LTRIM (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a character expression after it removes leading blanks.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LTRIM ( character_expression )

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example uses LTRIM to remove leading spaces from a character variable.

DECLARE @string_to_trim varchar(60);
SET @string_to_trim = '     Five spaces are at the beginning of this
   string.';
SELECT 'Here is the string without the leading spaces: ' + 
   LTRIM(@string_to_trim);
GO

Here is the result set.

------------------------------------------------------------------------
Here is the string without the leading spaces: Five spaces are at the beginning of this string.             

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

NCHAR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the Unicode character with the specified integer code, as defined by the Unicode standard.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

NCHAR ( integer_expression )

integer_expression

When the collation of the database does not contain the supplementary character (SC) flag, this is a positive whole number from 0 through 65535 (0 through 0xFFFF). If a value outside this range is specified, NULL is returned. For more information about supplementary characters, see Collation and Unicode Support.

When the collation of the database supports the supplementary character (SC) flag, this is a positive whole number from 0 through 1114111 (0 through 0x10FFFF). If a value outside this range is specified, NULL is returned.

nchar(1) when the default database collation does not support supplementary characters.

nvarchar(2) when the default database collation supports supplementary characters.

If the parameter integer_expression lies in the range 0 - 0xFFFF, only one character is returned. For higher values, NCHAR returns the corresponding surrogate pair. Do not construct a surrogate pair by using NCHAR(<High surrogate>) + NCHAR(<Low Surrogate>). Instead, use a database collation that supports supplementary characters and then specify the Unicode codepoint for the surrogate pair. The following example demonstrates both the old style method of constructing a surrogate pair and the preferred method of specifying the Unicode codepoint.

CREATE DATABASE test COLLATE Finnish_Swedish_100_CS_AS_SC;
DECLARE @d nvarchar(10) = N'𣅿'; 
-– Old style method.
SELECT NCHAR(0xD84C) + NCHAR(0xDD7F); 

-- Preferred method. 
SELECT NCHAR(143743); 

-- Alternative preferred method.
SELECT NCHAR(UNICODE(@d));  

The following example uses the UNICODE and NCHAR functions to print the UNICODE value and the NCHAR (Unicode character) of the second character of the København character string, and to print the actual second character, ø.

DECLARE @nstring nchar(8);
SET @nstring = N'København';
SELECT UNICODE(SUBSTRING(@nstring, 2, 1)), 
   NCHAR(UNICODE(SUBSTRING(@nstring, 2, 1)));
GO

Here is the result set.

----------- - 
248         ø
(1 row(s) affected)

The following example uses the SUBSTRING, UNICODE, CONVERT, and NCHAR functions to print the character number, the Unicode character, and the UNICODE value of each character in the string København.

-- The @position variable holds the position of the character currently
-- being processed. The @nstring variable is the Unicode character 
-- string to process.
DECLARE @position int, @nstring nchar(9);
-- Initialize the current position variable to the first character in 
-- the string.
SET @position = 1;
-- Initialize the character string variable to the string to process.
-- Notice that there is an N before the start of the string. This 
-- indicates that the data following the N is Unicode data.
SET @nstring = N'København';
-- Print the character number of the position of the string you are at, 
-- the actual Unicode character you are processing, and the UNICODE 
-- value for this particular character.
PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value';
WHILE @position <= DATALENGTH(@nstring)
   BEGIN
   SELECT @position, 
      NCHAR(UNICODE(SUBSTRING(@nstring, @position, 1))),
      CONVERT(NCHAR(17), SUBSTRING(@nstring, @position, 1)),
      UNICODE(SUBSTRING(@nstring, @position, 1))
   SELECT @position = @position + 1
   END;
GO

Here is the result set.

Character # Unicode Character UNICODE Value
                                               
----------- ---- ----------------- ----------- 
1           K    K                 75

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
2           ø    ø                 248

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
3           b    b                 98

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
4           e    e                 101

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
5           n    n                 110

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
6           h    h                 104

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
7           a    a                 97

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
8           v    v                 118

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
9           n    n                 110

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
10          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
11          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
12          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
13          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
14          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
15          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
16          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
17          NULL                   NULL

(1 row(s) affected)

                                               
----------- ---- ----------------- ----------- 
18          NULL                   NULL

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

PATINDEX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

PATINDEX ( '%pattern%' , expression )

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters. 

expression

Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.

bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.

If either pattern or expression is NULL, PATINDEX returns NULL.

PATINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

When using SC collations, the return value will count any UTF-16 surrogate pairs in the expression parameter as a single character. For more information, see Collation and Unicode Support.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in PATINDEX.

The following example finds the position at which the pattern ensure starts in a specific row of the DocumentSummary column in the Document table in the AdventureWorks2012 database.

SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
GO 

Here is the result set.

-----------

64

(1 row(s) affected)

If you do not restrict the rows to be searched by using a WHERE clause, the query returns all rows in the table and reports nonzero values for those rows in which the pattern was found, and zero for all rows in which the pattern was not found.

The following example uses % and _ wildcards to find the position at which the pattern 'en', followed by any one character and 'ure' starts in the specified string (index starts at 1):

  SELECT PATINDEX('%en_ure%', 'please ensure the door is locked');
 

Here is the result set.

------------

8

PATINDEX works just like LIKE, so you can use any of the wildcards. You do not have to enclose the pattern between percents. PATINDEX('a%', 'abc') returns 1 and PATINDEX('%a', 'cba') returns 3.

Unlike LIKE, PATINDEX returns a position, similar to what CHARINDEX does.

The following example uses the COLLATE function to explicitly specify the collation of the expression that is searched.

USE tempdb;
GO
SELECT PATINDEX ( '%ein%', 'Das ist ein Test'  COLLATE Latin1_General_BIN) ;
GO

The following example uses a variable to pass a value to the pattern parameter. This example uses the AdventureWorks2012 database.

DECLARE @MyValue varchar(10) = 'safety'; 
SELECT PATINDEX('%' + @MyValue + '%', DocumentSummary) 
FROM Production.Document
WHERE DocumentNode = 0x7B40;

Here is the result set.

------------

22

© 2015 Microsoft
Export (0) Print
Expand All

QUOTENAME (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

QUOTENAME ( 'character_string' [ , 'quote_character' ] ) 

'character_string'

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

'quote_character'

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

nvarchar(258)

The following example takes the character string abc[]def and uses the [ and ] characters to create a valid SQL Server delimited identifier.

SELECT QUOTENAME('abc[]def');

Here is the result set.

[abc[]]def]

(1 row(s) affected)

Notice that the right bracket in the string abc[]def is doubled to indicate an escape character.

© 2015 Microsoft
Export (0) Print
Expand All

REPLACE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Replaces all occurrences of a specified string value with another string value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

REPLACE ( string_expression , string_pattern , string_replacement )

string_expression

Is the string expression to be searched. string_expression can be of a character or binary data type.

string_pattern

Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.

string_replacement

Is the replacement string. string_replacement can be of a character or binary data type.

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.

Returns NULL if any one of the arguments is NULL.

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in REPLACE.

The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx');
GO

Here is the result set.

------------
abxxxfghixxx
(1 row(s) affected)

The following example uses the COLLATE function.

SELECT REPLACE('This is a Test'  COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO

Here is the result set.

------------
This is a desk
(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

REPLICATE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Repeats a string value a specified number of times.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

REPLICATE ( string_expression ,integer_expression ) 

string_expression

Is an expression of a character string or binary data type. string_expression can be either character or binary data.

System_CAPS_noteNote

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

integer_expression

Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.

Returns the same type as string_expression.

The following example replicates a 0 character four times in front of a production line code in the AdventureWorks2012 database.

SELECT [Name]
, REPLICATE('0', 4) + [ProductLine] AS 'Line Code'
FROM [Production].[Product]
WHERE [ProductLine] = 'T'
ORDER BY [Name];
GO

Here is the result set.

Name                                               Line Code
-------------------------------------------------- ---------
HL Touring Frame - Blue, 46                        0000T 
HL Touring Frame - Blue, 50                        0000T 
HL Touring Frame - Blue, 54                        0000T 
HL Touring Frame - Blue, 60                        0000T 
HL Touring Frame - Yellow, 46                      0000T 
HL Touring Frame - Yellow, 50                      0000T
...

The following example left pads numbers to a specified length as they are converted from a numeric data type to character or Unicode.

IF EXISTS(SELECT name FROM sys.tables
      WHERE name = 't1')
   DROP TABLE t1;
GO
CREATE TABLE t1 
(
 c1 varchar(3),
 c2 char(3)
);
GO
INSERT INTO t1 VALUES ('2', '2'), ('37', '37'),('597', '597');
GO
SELECT REPLICATE('0', 3 - DATALENGTH(c1)) + c1 AS 'Varchar Column',
       REPLICATE('0', 3 - DATALENGTH(c2)) + c2 AS 'Char Column'
FROM t1;
GO

Here is the result set.

Varchar Column        Char Column
--------------------  ------------
002                   2  
037                   37 
597                   597

(3 row(s) affected)


© 2015 Microsoft
Export (0) Print
Expand All

REVERSE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the reverse order of a string value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

REVERSE ( string_expression )

string_expression

string_expression is an expression of a string or binary data type. string_expression can be a constant, variable, or column of either character or binary data.

varchar or nvarchar

string_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert string_expression.

When using SC collations, the REVERSE function will not reverse the order of two halves of a surrogate pair.

The following example returns all contact first names with the characters reversed. This example uses the AdventureWorks2012 database.

SELECT FirstName, REVERSE(FirstName) AS Reverse
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Here is the result set.

FirstName Reverse

-------------- --------------

Ken neK

Rob boR

Roberto otreboR

Terri irreT

(4 row(s) affected)

The following example reverses the characters in a variable.

DECLARE @myvar varchar(10);
SET @myvar = 'sdrawkcaB';
SELECT REVERSE(@myvar) AS Reversed ;
GO

The following example makes an implicit conversion from an int data type into varchar data type and then reverses the result.

SELECT REVERSE(1234) AS Reversed ;
GO
© 2015 Microsoft
Export (0) Print
Expand All

RIGHT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the right part of a character string with the specified number of characters.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

RIGHT ( character_expression , integer_expression )

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

Returns varchar when character_expression is a non-Unicode character data type.

Returns nvarchar when character_expression is a Unicode character data type.

When using SC collations, the RIGHT function counts a UTF-16 surrogate pair as a single character. For more information, see Collation and Unicode Support.

The following example returns the five rightmost characters of the first name for each person in the AdventureWorks2012 database.

SELECT RIGHT(FirstName, 5) AS 'First Name'
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Here is the result set.

First Name
----------
Ken
Terri
berto
Rob

(4 row(s) affected)

© 2015 Microsoft
Export (0) Print
Expand All

RTRIM (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a character string after truncating all trailing blanks.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

RTRIM ( character_expression )

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example takes a string of characters that has spaces at the end of the sentence, and returns the text without the spaces at the end of the sentence.

SELECT RTRIM('Removes trailing spaces.   ');

Here is the result set.

-------------------------------------------------------------------------

Removes trailing spaces.

The following example demonstrates how to use RTRIM to remove trailing spaces from a character variable.

DECLARE @string_to_trim varchar(60);
SET @string_to_trim = 'Four spaces are after the period in this sentence.    ';
SELECT @string_to_trim + ' Next string.';
SELECT RTRIM(@string_to_trim) + ' Next string.';
GO

Here is the result set.

-------------------------------------------------------------------------

Four spaces are after the period in this sentence.     Next string.

(1 row(s) affected)

-------------------------------------------------------------------------

Four spaces are after the period in this sentence. Next string.

(1 row(s) affected)

© 2015 Microsoft
Export (0) Print
Expand All

SOUNDEX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

Applies to: SQL Server (SQL Server 2008 through current version) , Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SOUNDEX ( character_expression )

character_expression

Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken. The first character of the code is the first character of character_expression, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the expression. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the string. Zeroes are added at the end if necessary to produce a four-character code. For more information about the SOUNDEX code, see The Soundex Indexing System.

SOUNDEX codes from different strings can be compared to see how similar the strings sound when spoken. The DIFFERENCE function performs a SOUNDEX on two strings, and returns an integer that represents how similar the SOUNDEX codes are for those strings.

SOUNDEX is collation sensitive. String functions can be nested.

In previous versions of SQL Server, the SOUNDEX function applied a subset of the SOUNDEX rules. Under database compatibility level 110 or higher, SQL Server applies a more complete set of the rules.

After upgrading to compatibility level 110 or higher, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function.

  • A heap that contains a persisted computed column defined with SOUNDEX cannot be queried until the heap is rebuilt by running the statement ALTER TABLE <table> REBUILD.

  • CHECK constraints defined with SOUNDEX are disabled upon upgrade. To enable the constraint, run the statement ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL.

  • Indexes (including indexed views) that contain a persisted computed column defined with SOUNDEX cannot be queried until the index is rebuilt by running the statement ALTER INDEX ALL ON <object> REBUILD.

The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

-- Using SOUNDEX
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');

Here is the result set. Valid for a Latin1_General collation.

----- ----- 
S530  S530  

(1 row(s) affected)

The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.

-- Using DIFFERENCE
SELECT DIFFERENCE('Smithers', 'Smythers');
GO

Here is the result set. Valid for a Latin1_General collation.

----------- 
4           

(1 row(s) affected)

In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.

SELECT DIFFERENCE('Anothers', 'Brothers');
GO

Here is the result set. Valid for a Latin1_General collation.

----------- 
2           

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SPACE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a string of repeated spaces.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SPACE ( integer_expression )

integer_expression

Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

For more information, see Expressions (Transact-SQL)

To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE.

The following example trims the last names and concatenates a comma, two spaces, and the first names of people listed in the Person table in AdventureWorks2012.

USE AdventureWorks2012;
GO
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName)
FROM Person.Person
ORDER BY LastName, FirstName;
GO
© 2015 Microsoft
Export (0) Print
Expand All

STR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns character data converted from numeric data.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

STR ( float_expression [ , length [ , decimal ] ] )

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

decimal

Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.

If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12. This is right-justified in the result set. However, STR(1223,2) truncates the result set to **. String functions can be nested.

System_CAPS_noteNote

To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

The following example converts an expression that is made up of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.

SELECT STR(123.45, 6, 1);
GO

Here is the result set.

------
 123.5

(1 row(s) affected)

When the expression exceeds the specified length, the string returns ** for the specified length.

SELECT STR(123.45, 2, 2);
GO

Here is the result set.

--
**

(1 row(s) affected)

Even when numeric data is nested within STR, the result is character data with the specified format.

SELECT STR (FLOOR (123.45), 8, 3;)
GO

Here is the result set.

--------
 123.000

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

STUFF (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

STUFF ( character_expression , start , length , replaceWith_expression )

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start

Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

length

Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

replaceWith_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

When using SC collations, both character_expression and replaceWith_expression can include surrogate pairs. The length parameter will count each surrogate in character_expression as a single character.

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO

Here is the result set.

--------- 
aijklmnef 

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SUBSTRING (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns part of a character, binary, text, or image expression in SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SUBSTRING ( expression ,start , length )

expression

Is a character, binary, text, ntext, or image expression.

start

Is an integer or bigint expression that specifies where the returned characters start. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.

Specified expression

Return type

char/varchar/text

varchar

nchar/nvarchar/ntext

nvarchar 

binary/varbinary/image

varbinary

The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.

The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647.

When using supplementary character (SC) collations, both start and length count each surrogate pair in expression as a single character. For more information, see Collation and Unicode Support.

The following example shows how to return only a part of a character string. From the Person table in the AdventureWorks2012 database, this query returns the last name in one column with only the first initial in the second column.

SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial
FROM Person.Person
WHERE LastName like 'Barl%'
ORDER BY LastName;

Here is the result set.

LastName      Initial

--------      -------

Barley        R

Barlow        B

(2 row(s) affected)

Here is how to display the second, third, and fourth characters of the string constant abcdef.

SELECT x = SUBSTRING('abcdef', 2, 3);

Here is the result set.

x

----------

bcd

(1 row(s) affected)

System_CAPS_noteNote

To run the following examples, you must install the pubs database.

The following example shows how to return the first 10 characters from each of a text and image data column in the pub_info table of the pubs database. text data is returned as varchar, and image data is returned as varbinary.

USE pubs;
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, 
   SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = '1756';

Here is the result set.

pub_id logo pr_info

------ ---------------------- ----------

1756 0x474946383961E3002500 This is sa

(1 row(s) affected)

The following example shows the effect of SUBSTRING on both text and ntext data. First, this example creates a new table in the pubs database named npub_info. Second, the example creates the pr_info column in the npub_info table from the first 80 characters of the pub_info.pr_info column and adds an ü as the first character. Lastly, an INNER JOIN retrieves all publisher identification numbers and the SUBSTRING of both the text and ntext publisher information columns.

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES 
      WHERE table_name = 'npub_info')
   DROP TABLE npub_info;
GO
-- Create npub_info table in pubs database. Borrowed from instpubs.sql.
USE pubs;
GO
CREATE TABLE npub_info
(
 pub_id char(4) NOT NULL
    REFERENCES publishers(pub_id)
    CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED,
pr_info ntext NULL
);

GO

-- Fill the pr_info column in npub_info with international data.
RAISERROR('Now at the inserts to pub_info...',0,1);

GO

INSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database')
,('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa')
,('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da')
,('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database')
,('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d')
,('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab')
,('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i')
,('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data');
GO
-- Join between npub_info and pub_info on pub_id.
SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info,
   SUBSTRING(npr.pr_info, 1, 35) AS npr_info
FROM pub_info pr INNER JOIN npub_info npr
   ON pr.pub_id = npr.pub_id
ORDER BY pr.pub_id ASC;
© 2015 Microsoft
Export (0) Print
Expand All

UNICODE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

UNICODE ( 'ncharacter_expression' )

'ncharacter_expression'

Is an nchar or nvarchar expression.

In versions of SQL Server earlier than SQL Server 2012 and in Azure SQL Database, the UNICODE function returns a UCS-2 codepoint in the range 0 through 0xFFFF. In SQL Server 2012 and later editions, when using SC collations, UNICODE returns a UTF-16 codepoint in the range 0 through 0x10FFFF.

The following example uses the UNICODE and NCHAR functions to print the UNICODE value of the first character of the Åkergatan 24-character string, and to print the actual first character, Å.

DECLARE @nstring nchar(12);
SET @nstring = N'Åkergatan 24';
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));

Here is the result set.

----------- - 
197         Å

The following example uses the SUBSTRING, UNICODE, and CONVERT functions to print the character number, the Unicode character, and the UNICODE value of each of the characters in the string Åkergatan 24.

-- The @position variable holds the position of the character currently
-- being processed. The @nstring variable is the Unicode character 
-- string to process.
DECLARE @position int, @nstring nchar(12);
-- Initialize the current position variable to the first character in 
-- the string.
SET @position = 1;
-- Initialize the character string variable to the string to process. 
-- Notice that there is an N before the start of the string, which 
-- indicates that the data following the N is Unicode data.
SET @nstring = N'Åkergatan 24';
-- Print the character number of the position of the string you are at, 
-- the actual Unicode character you are processing, and the UNICODE 
-- value for this particular character.
PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value';
WHILE @position <= DATALENGTH(@nstring)
-- While these are still characters in the character string,
   BEGIN;
   SELECT @position, 
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),
      UNICODE(SUBSTRING(@nstring, @position, 1));
   SELECT @position = @position + 1;
   END;

Here is the result set.

Character # Unicode Character UNICODE Value
                                          
----------- ----------------- ----------- 
1           Å                 197         
                                          
----------- ----------------- ----------- 
2           k                 107         
                                          
----------- ----------------- ----------- 
3           e                 101         
                                          
----------- ----------------- ----------- 
4           r                 114         
                                          
----------- ----------------- ----------- 
5           g                 103         
                                          
----------- ----------------- ----------- 
6           a                 97          
                                          
----------- ----------------- ----------- 
7           t                 116         
                                          
----------- ----------------- ----------- 
8           a                 97          
                                          
----------- ----------------- ----------- 
9           n                 110         
                                          
----------- ----------------- ----------- 
10                            32          
                                          
----------- ----------------- ----------- 
11          2                 50          
                                          
----------- ----------------- ----------- 
12          4                 52
© 2015 Microsoft
Export (0) Print
Expand All

UPPER (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a character expression with lowercase character data converted to uppercase.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

UPPER ( character_expression )

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example uses the UPPER and RTRIM functions to return the last name of people in the Person table in the AdventureWorks2012 database so that it is uppercase, trimmed, and concatenated with the first name.

SELECT UPPER(RTRIM(LastName)) + ', ' + FirstName AS Name
FROM Person.Person
ORDER BY LastName;
GO
© 2015 Microsoft
Export (0) Print
Expand All

Mathematical Functions (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

The following scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value:

ABS           

DEGREES           

RAND           

ACOS           

EXP           

ROUND           

ASIN           

FLOOR           

SIGN           

ATAN           

LOG           

SIN           

ATN2           

LOG10           

SQRT           

CEILING           

PI           

SQUARE

COS           

POWER           

TAN           

COT           

RADIANS           

 

System_CAPS_noteNote

Arithmetic functions, such as ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, and SIGN, return a value having the same data type as the input value. Trigonometric and other functions, including EXP, LOG, LOG10, SQUARE, and SQRT, cast their input values to float and return a float value.

All mathematical functions, except for RAND, are deterministic functions. This means they return the same results each time they are called with a specific set of input values. RAND is deterministic only when a seed parameter is specified. For more information about function determinism, see Deterministic and Nondeterministic Functions.

© 2015 Microsoft
Export (0) Print
Expand All

ABS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ABS ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category.

Returns the same type as numeric_expression.

The following example shows the results of using the ABS function on three different numbers.

SELECT ABS(-1.0), ABS(0.0), ABS(1.0);

Here is the result set.

---- ---- ----
1.0  .0   1.0

The ABS function can produce an overflow error when the absolute value of a number is greater than the largest number that can be represented by the specified data type. For example, the int data type can hold only values that range from -2,147,483,648 to 2,147,483,647. Computing the absolute value for the signed integer -2,147,483,648 causes an overflow error because its absolute value is greater than the positive range for the int data type.

DECLARE @i int;
SET @i = -2147483648;
SELECT ABS(@i);
GO

Here is the error message:

"Msg 8115, Level 16, State 2, Line 3"

"Arithmetic overflow error converting expression to data type int."

© 2015 Microsoft
Export (0) Print
Expand All

ACOS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

A mathematical function that returns the angle, in radians, whose cosine is the specified float expression; also called arccosine.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ACOS ( float_expression )

float_expression

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

The following example returns the ACOS of the specified number.

SET NOCOUNT OFF;
DECLARE @cos float;
SET @cos = -1.0;
SELECT 'The ACOS of the number is: ' + CONVERT(varchar, ACOS(@cos));

Here is the result set.

--------------------------------- 
The ACOS of the number is: 3.14159 

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

ASIN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ASIN ( float_expression )

float_expression

Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.

The following example takes a float expression and returns the ASIN of the specified angle.

/* The first value will be -1.01. This fails because the value is 
outside the range.*/
DECLARE @angle float
SET @angle = -1.01
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO

-- The next value is -1.00.
DECLARE @angle float
SET @angle = -1.00
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO

-- The next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The ASIN of the angle is: ' + CONVERT(varchar, ASIN(@angle))
GO

Here is the result set.

-------------------------
.Net SqlClient Data Provider: Msg 3622, Level 16, State 1, Line 3
A domain error occurred.

                                                         
--------------------------------- 
The ASIN of the angle is: -1.5708                        

(1 row(s) affected)

                                                         
---------------------------------- 
The ASIN of the angle is: 0.147811                       

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

ATAN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the angle in radians whose tangent is a specified float expression. This is also called arctangent.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ATAN ( float_expression )

float_expression

Is an expression of the type float or of a type that can be implicitly converted to float.

The following example takes a float expression and returns the ATAN of the specified angle.

SELECT 'The ATAN of -45.01 is: ' + CONVERT(varchar, ATAN(-45.01))
SELECT 'The ATAN of -181.01 is: ' + CONVERT(varchar, ATAN(-181.01))
SELECT 'The ATAN of 0 is: ' + CONVERT(varchar, ATAN(0))
SELECT 'The ATAN of 0.1472738 is: ' + CONVERT(varchar, ATAN(0.1472738))
SELECT 'The ATAN of 197.1099392 is: ' + CONVERT(varchar, ATAN(197.1099392))
GO

Here is the result set.

                                                         
------------------------------- 
The ATAN of -45.01 is: -1.54858                       

(1 row(s) affected)

-------------------------------- 
The ATAN of -181.01 is: -1.56527                       

(1 row(s) affected)

-------------------------------- 
The ATAN of 0 is: 0                              

(1 row(s) affected)

---------------------------------- 
The ATAN of 0.1472738 is: 0.146223                       

(1 row(s) affected)

                                                         
----------------------------------- 
The ATAN of 197.1099392 is: 1.56572                        

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

ATN2 (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ATN2 ( float_expression , float_expression )

float_expression

Is an expression of the float data type.

The following example calculates the ATN2 for the specified x and y components.

DECLARE @x float = 35.175643, @y float = 129.44;
SELECT 'The ATN2 of the angle is: ' + CONVERT(varchar,ATN2(@x,@y ));
GO

Here is the result set.

The ATN2 of the angle is: 0.265345                       
(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

CEILING (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the smallest integer greater than, or equal to, the specified numeric expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

CEILING ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Returns the same type as numeric_expression.

The following example shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0);
GO

Here is the result set.

--------- --------- ------------------------- 
124.00    -123.00    0.00                     

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

COS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Is a mathematical function that returns the trigonometric cosine of the specified angle, in radians, in the specified expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

COS ( float_expression )

float_expression

Is an expression of type float.

The following example returns the COS of the specific angle.

DECLARE @angle float;
SET @angle = 14.78;
SELECT 'The COS of the angle is: ' + CONVERT(varchar,COS(@angle));
GO

Here is the result set.

The COS of the angle is: -0.599465                      

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

COT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

A mathematical function that returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

COT ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The following example returns the COT for the specific angle.

DECLARE @angle float;
SET @angle = 124.1332;
SELECT 'The COT of the angle is: ' + CONVERT(varchar,COT(@angle));
GO

Here is the result set.

The COT of the angle is: -0.040312              

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DEGREES (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the corresponding angle in degrees for an angle specified in radians.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DEGREES ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Returns the same type as numeric_expression.

The following example returns the number of degrees in an angle of PI/2 radians.

SELECT 'The number of degrees in PI/2 radians is: ' + 
CONVERT(varchar, DEGREES((PI()/2)));
GO

Here is the result set.

The number of degrees in PI/2 radians is 90       

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

EXP (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the exponential value of the specified float expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

EXP ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The constant e (2.718281…), is the base of natural logarithms.

The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

The exponential of the natural logarithm of a number is the number itself: EXP (LOG (n)) = n. And the natural logarithm of the exponential of a number is the number itself: LOG (EXP (n)) = n.

The following example declares a variable and returns the exponential value of the specified variable (10) with a text description.

DECLARE @var float
SET @var = 10
SELECT 'The EXP of the variable is: ' + CONVERT(varchar,EXP(@var))
GO

Here is the result set.

----------------------------------------------------------
The EXP of the variable is: 22026.5
(1 row(s) affected)

The following example returns the exponential value of the natural logarithm of 20 and the natural logarithm of the exponential of 20. Because these functions are inverse functions of one another, the return value in both cases is 20.

SELECT EXP( LOG(20)), LOG( EXP(20))
GO

Here is the result set.

---------------------- ----------------------
20                     20

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

FLOOR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the largest integer less than or equal to the specified numeric expression.

Applies to: SQL Server (SQL Server 2008 through current version) , Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

FLOOR ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Returns the same type as numeric_expression.

The following example shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);

The result is the integer part of the calculated value in the same data type as numeric_expression.

---------      ---------     -----------
123            -124          123.0000   
© 2015 Microsoft
Export (0) Print
Expand All

LOG (Transact-SQL)

 

Applies To: Azure SQL Database, SQL Data Warehouse, SQL Server 2014, SQL Server 2016 Preview

Returns the natural logarithm of the specified float expression in SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

-- SQL Server Syntax
LOG ( float_expression [, base ] )
-- Windows Azure SQL Database Syntax
LOG ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

base

Optional integer argument that sets the base for the logarithm.

Applies to: SQL Server 2012 through SQL Server 2016.

By default, LOG() returns the natural logarithm. Starting with SQL Server 2012, you can change the base of the logarithm to another value by using the optional base parameter.

The natural logarithm is the logarithm to the base e, where e is an irrational constant approximately equal to 2.718281828.

The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( n ) ) = n.

A. Calculating the logarithm for a number.

The following example calculates the LOG for the specified float expression.

DECLARE @var float = 10;
SELECT 'The LOG of the variable is: ' + CONVERT(varchar, LOG(@var));
GO

Here is the result set.

-------------------------------------
The LOG of the variable is: 2.30259

(1 row(s) affected)

B. Calculating the logarithm of the exponent of a number.

The following example calculates the LOG for the exponent of a number.

SELECT LOG (EXP (10));

Here is the result set.

----------------------------------
10
(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

LOG10 (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the base-10 logarithm of the specified float expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

LOG10 ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(n) = n.

The following example calculates the LOG10 of the specified variable.

DECLARE @var float;
SET @var = 145.175643;
SELECT 'The LOG10 of the variable is: ' + CONVERT(varchar,LOG10(@var));
GO

Here is the result set.

The LOG10 of the variable is: 2.16189    

(1 row(s) affected)

The following example returns the result of raising a base-10 logarithm to a specified power.

SELECT POWER (10, LOG10(5)); 

Here is the result set.

-----------
5

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

PI (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the constant value of PI.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

PI ( )

The following example returns the value of PI.

SELECT PI();
GO

Here is the result set.

------------------------
3.14159265358979

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

POWER (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the value of the specified expression to the specified power.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

POWER ( float_expression , y )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

y

Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

The following example demonstrates raising a number to the power of 3 (the cube of the number).

DECLARE @input1 float;
DECLARE @input2 float;
SET @input1= 2;
SET @input2 = 2.5;
SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2;

Here is the result set.

Result1                Result2
---------------------- ----------------------
8                      15.625

(1 row(s) affected)

The following example shows how the float_expression preserves the data type which can return unexpected results.

SELECT 
POWER(CAST(2.0 AS float), -100.0) AS FloatResult,
POWER(2, -100.0) AS IntegerResult,
POWER(CAST(2.0 AS int), -100.0) AS IntegerResult,
POWER(2.0, -100.0) AS Decimal1Result,
POWER(2.00, -100.0) AS Decimal2Result,
POWER(CAST(2.0 AS decimal(5,2)), -100.0) AS Decimal2Result;
GO

Here is the result set.

FloatResult            IntegerResult IntegerResult Decimal1Result Decimal2Result Decimal2Result
---------------------- ------------- ------------- -------------- -------------- --------------
7.88860905221012E-31   0             0             0.0            0.00           0.00

The following example returns POWER results for 2.

DECLARE @value int, @counter int;
SET @value = 2;
SET @counter = 1;

WHILE @counter < 5
   BEGIN
      SELECT POWER(@value, @counter)
      SET NOCOUNT ON
      SET @counter = @counter + 1
      SET NOCOUNT OFF
   END;
GO

Here is the result set.

----------- 
2           

(1 row(s) affected)

----------- 
4           

(1 row(s) affected)

----------- 
8           

(1 row(s) affected)

----------- 
16          

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

RADIANS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns radians when a numeric expression, in degrees, is entered.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

RADIANS ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Returns the same type as numeric_expression.

The following example returns a result of 0.0 because the numeric expression to convert to radians is too small for the RADIANS function.

SELECT RADIANS(1e-307)
GO

Here is the result set.

------------------- 
0.0                      
(1 row(s) affected)

The following example takes a float expression and returns the RADIANS of the specified angle.

-- First value is -45.01.
DECLARE @angle float
SET @angle = -45.01
SELECT 'The RADIANS of the angle is: ' +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is -181.01.
DECLARE @angle float
SET @angle = -181.01
SELECT 'The RADIANS of the angle is: ' +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.00.
DECLARE @angle float
SET @angle = 0.00
SELECT 'The RADIANS of the angle is: ' +
   CONVERT(varchar, RADIANS(@angle))
GO
-- Next value is 0.1472738.
DECLARE @angle float
SET @angle = 0.1472738
SELECT 'The RADIANS of the angle is: ' +
    CONVERT(varchar, RADIANS(@angle))
GO
-- Last value is 197.1099392.
DECLARE @angle float
SET @angle = 197.1099392
SELECT 'The RADIANS of the angle is: ' +
   CONVERT(varchar, RADIANS(@angle))
GO

Here is the result set.

--------------------------------------- 
The RADIANS of the angle is: -0.785573                      
(1 row(s) affected)
--------------------------------------- 
The RADIANS of the angle is: -3.15922                       
(1 row(s) affected)
--------------------------------------- 
The RADIANS of the angle is: 0                              
(1 row(s) affected)
--------------------------------------- 
The RADIANS of the angle is: 0.00257041                     
 (1 row(s) affected)
--------------------------------------- 
The RADIANS of the angle is: 3.44022                        
(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

RAND (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a pseudo-random float value from 0 through 1, exclusive.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

RAND ( [ seed ] )

seed

Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND() 

The following example produces four different random numbers that are generated by the RAND function.

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO
© 2015 Microsoft
Export (0) Print
Expand All

ROUND (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a numeric value, rounded to the specified length or precision.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ROUND ( numeric_expression , length [ ,function ] )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length

Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

function

Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Returns the following data types.

Expression result

Return type

tinyint

int

smallint

int

int

int

bigint

bigint

decimal and numeric category (p, s)

decimal(p, s)

money and smallmoney category

money

float and real category

float

ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

Example

Result

ROUND(748.58, -4)

0

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Examples

Result

ROUND(748.58, -1)

750.00

ROUND(748.58, -2)

700.00

ROUND(748.58, -3)

Results in an arithmetic overflow, because 748.58 defaults to decimal(5,2), which cannot return 1000.00.

To round up to 4 digits, change the data type of the input. For example:

SELECT ROUND(CAST (748.58 AS decimal (6,2)),-3);

1000.00

The following example shows two expressions that demonstrate by using ROUND the last digit is always an estimate.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3);
GO

Here is the result set.

----------- -----------
123.9990    124.0000    

The following example shows rounding and approximations.

SELECT ROUND(123.4545, 2);
GO
SELECT ROUND(123.45, -2);
GO

Here is the result set.

----------

123.4500

(1 row(s) affected)

--------

100.00

(1 row(s) affected)

The following example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

SELECT ROUND(150.75, 0);
GO
SELECT ROUND(150.75, 0, 1);
GO

Here is the result set.

--------
151.00

(1 row(s) affected)

--------
150.00

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SIGN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SIGN ( numeric_expression )

numeric_expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Specified expression

Return type

bigint

bigint

int/smallint/tinyint

int

money/smallmoney

money

numeric/decimal

numeric/decimal

Other types

float

The following example returns the SIGN values of numbers from -1 to 1.

DECLARE @value real
SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)
      SET NOCOUNT ON
      SELECT @value = @value + 1
      SET NOCOUNT OFF
   END
SET NOCOUNT OFF
GO

Here is the result set.

(1 row(s) affected)

                         
------------------------ 
-1.0                     

(1 row(s) affected)

                         
------------------------ 
0.0                      

(1 row(s) affected)

                         
------------------------ 
1.0                      

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SIN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SIN ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The following example calculates the SIN for a specified angle.

DECLARE @angle float;
SET @angle = 45.175643;
SELECT 'The SIN of the angle is: ' + CONVERT(varchar,SIN(@angle));
GO

Here is the result set.

The SIN of the angle is: 0.929607                       

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SQRT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the square root of the specified float value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SQRT ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The following example returns the square root of numbers between 1.00 and 10.00.

DECLARE @myvalue float;
SET @myvalue = 1.00;
WHILE @myvalue < 10.00
   BEGIN
      SELECT SQRT(@myvalue);
      SET @myvalue = @myvalue + 1
   END;
GO

Here is the result set.

------------------------ 
1.0                      
------------------------ 
1.4142135623731          
------------------------ 
1.73205080756888         
------------------------ 
2.0                      
------------------------ 
2.23606797749979         
------------------------ 
2.44948974278318         
------------------------ 
2.64575131106459         
------------------------ 
2.82842712474619         
------------------------ 
3.0
© 2015 Microsoft
Export (0) Print
Expand All

SQUARE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the square of the specified float value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SQUARE ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float.

The following example returns the volume of a cylinder having a radius of 1 inch and a height of 5 inches.

DECLARE @h float, @r float;
SET @h = 5;
SET @r = 1;
SELECT PI()* SQUARE(@r)* @h AS 'Cyl Vol';

Here is the result set.

Cyl Vol
--------------------------
15.707963267948966
© 2015 Microsoft
Export (0) Print
Expand All

TAN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the tangent of the input expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

TAN ( float_expression )

float_expression

Is an expression of type float or of a type that can be implicitly converted to float, interpreted as number of radians.

The following example returns the tangent of PI()/2.

SELECT TAN(PI()/2);

Here is the result set.

----------------------
1.6331778728383844E+16
© 2015 Microsoft
Export (0) Print
Expand All

Logical Functions (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

The following scalar functions perform logical operations.

© 2015 Microsoft
Export (0) Print
Expand All

CHOOSE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the item at the specified index from a list of values in SQL Server.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

CHOOSE ( index, val_1, val_2 [, val_n ] )

index

Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

val_1 … val_n

List of comma separated values of any data type.

Returns the data type with the highest precedence from the set of types passed to the function. For more information, see Data Type Precedence (Transact-SQL).

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.


The following example returns the third item from the list of values that is provided.

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

Here is the result set.

Result
-------------
Developer

(1 row(s) affected)

The following example returns a simple character string based on the value in the ProductCategoryID column.

USE AdventureWorks2012;
GO
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1
FROM Production.ProductCategory;

Here is the result set.

ProductCategoryID Expression1
----------------- -----------
3                 C
1                 A
2                 B
4                 D

(4 row(s) affected)

The following example returns the quarter in which an employee was hired. The MONTH function is used to return the month value from the column HireDate.

USE AdventureWorks2012;
GO
SELECT JobTitle, HireDate, CHOOSE(MONTH(HireDate),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer', 
                                                  'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Hired
FROM HumanResources.Employee
WHERE  YEAR(HireDate) > 2005
ORDER BY YEAR(HireDate); 

Here is the result set.

JobTitle                                           HireDate   Quarter_Hired
-------------------------------------------------- ---------- -------------
Sales Representative                               2006-11-01 Autumn
European Sales Manager                             2006-05-18 Spring
Sales Representative                               2006-07-01 Summer
Sales Representative                               2006-07-01 Summer
Sales Representative                               2007-07-01 Summer
Pacific Sales Manager                              2007-04-15 Spring
Sales Representative                               2007-07-01 Summer


© 2015 Microsoft
Export (0) Print
Expand All

IIF (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

IIF ( boolean_expression, true_value, false_value )

boolean_expression

A valid Boolean expression.

If this argument is not a Boolean expression, then a syntax error is raised.

true_value

Value to return if boolean_expression evaluates to true.

false_value

Value to return if boolean_expression evaluates to false.

Returns the data type with the highest precedence from the types in true_value and false_value. For more information, see Data Type Precedence (Transact-SQL).

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF. For more information, see CASE (Transact-SQL).

The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE expressions can be nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE expression, with all the behaviors of a remoted CASE expression.

DECLARE @a int = 45, @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

Here is the result set.

Result
--------
TRUE

(1 row(s) affected)

SELECT IIF ( 45 > 30, NULL, NULL ) AS Result;

The result of this statement is an error.

DECLARE @P INT = NULL, @S INT = NULL;
SELECT IIF ( 45 > 30, @p, @s ) AS Result;

Here is the result set.

Result
--------
NULL

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

Data Type Functions (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

The following scalar functions return information about various data type values.

© 2015 Microsoft
Export (0) Print
Expand All

DATALENGTH (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the number of bytes used to represent any expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DATALENGTH ( expression ) 

expression

Is an expression of any data type.

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise int.

DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

The DATALENGTH of NULL is NULL.

System_CAPS_noteNote

Compatibility levels can affect return values. For more information about compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).

The following example finds the length of the Name column in the Product table.

USE AdventureWorks2012;
GO
SELECT length = DATALENGTH(Name), Name
FROM Production.Product
ORDER BY Name;
GO
© 2015 Microsoft
Export (0) Print
Expand All

IDENT_CURRENT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

IDENT_CURRENT( 'table_name' )

table_name

Is the name of the table whose identity value is returned. table_name is varchar, with no default.

numeric(38,0)

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_CURRENT may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

Exception Condition

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

The following example returns the last identity value generated for the Person.Address table in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
SELECT IDENT_CURRENT ('Person.Address') AS Current_Identity;
GO

The following example shows the different identity values that are returned by IDENT_CURRENT, @@IDENTITY, and SCOPE_IDENTITY.

USE AdventureWorks2012;
GO
IF OBJECT_ID(N't6', N'U') IS NOT NULL 
    DROP TABLE t6;
GO
IF OBJECT_ID(N't7', N'U') IS NOT NULL 
    DROP TABLE t7;
GO
CREATE TABLE t6(id int IDENTITY);
CREATE TABLE t7(id int IDENTITY(100,1));
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT 
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END;
GO
--End of trigger definition

SELECT id FROM t6;
--IDs empty.

SELECT id FROM t7;
--ID is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the 
INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('t7');
/* Returns value inserted into t7, that is in the trigger.*/

SELECT IDENT_CURRENT('t6');
/* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.
SELECT @@IDENTITY;
/* Returns NULL because there has been no INSERT action 
up to this point in this session.*/

SELECT SCOPE_IDENTITY();
/* Returns NULL because there has been no INSERT action 
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('t7');
/* Returns the last value inserted into t7.*/
© 2015 Microsoft
Export (0) Print
Expand All

IDENT_INCR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the increment value (returned as numeric (@@MAXPRECISION,0)) specified during the creation of an identity column in a table or view that has an identity column.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

IDENT_INCR ( 'table_or_view' )

'table_or_view' 

Is an expression specifying the table or view to check for a valid identity increment value. table_or_view can be a character string constant enclosed in quotation marks, a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.

numeric

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_INCR may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

Exception Condition

The following example returns the increment value for the Person.Address table in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
SELECT IDENT_INCR('Person.Address') AS Identity_Increment;
GO

The following example returns the tables in the AdventureWorks2012 database that include an identity column with an increment value.

USE AdventureWorks2012;
GO
SELECT TABLE_SCHEMA, TABLE_NAME, 
   IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;

Here is a partial result set.

TABLE_SCHEMA        TABLE_NAME                IDENT_INCR

------------        ------------------------  ----------

Person              Address                            1

Production          ProductReview                      1

Production          TransactionHistory                 1

Person              AddressType                        1

Production          ProductSubcategory                 1

Person              vAdditionalContactInfo             1

dbo                 AWBuildVersion                     1

Production          BillOfMaterials                    1

© 2015 Microsoft
Export (0) Print
Expand All

IDENT_SEED (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the original seed value (returned as numeric(@@MAXPRECISION,0)) that was specified when an identity column in a table or a view was created. Changing the current value of an identity column by using DBCC CHECKIDENT does not change the value returned by this function.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

IDENT_SEED ( 'table_or_view' )

'table_or_view' 

Is an expression that specifies the table or view to check for a identity seed value. table_or_view can be a character string constant enclosed in quotation marks, a variable, a function, or a column name. table_or_view is char, nchar, varchar, or nvarchar.

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as IDENT_SEED may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

Exception Condition

The following example returns the seed value for the Person.Address table in the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
SELECT IDENT_SEED('Person.Address') AS Identity_Seed;
GO

The following example returns the tables in the AdventureWorks2012 database that include an identity column with a seed value.

USE AdventureWorks2012;
GO
SELECT TABLE_SCHEMA, TABLE_NAME, 
   IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL;
GO

Here is a partial result set.

TABLE_SCHEMA       TABLE_NAME                   IDENT_SEED

------------       ---------------------------  -----------

Person             Address                                1

Production         ProductReview                          1

Production         TransactionHistory                100000

Person             AddressType                            1

Production         ProductSubcategory                     1

Person             vAdditionalContactInfo                 1

dbo                AWBuildVersion                         1

© 2015 Microsoft
Export (0) Print
Expand All

IDENTITY (Function) (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

System_CAPS_noteNote

To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

IDENTITY (data_type [ , seed , increment ] ) AS column_name

data_type

Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.

seed

Is the integer value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.

increment

Is the integer value to add to the seed value for successive rows in the table.

column_name

Is the name of the column that is to be inserted into the new table.

Returns the same as data_type.

Because this function creates a column in a table, a name for the column must be specified in the select list in one of the following ways:

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;


The following example inserts all rows from the Contact table from the AdventureWorks2012database into a new table called NewContact. The IDENTITY function is used to start identification numbers at 100 instead of 1 in the NewContact table.

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Person.NewContact', N'U') IS NOT NULL
    DROP TABLE Person.NewContact;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO
SELECT  IDENTITY(smallint, 100, 1) AS ContactNum,
        FirstName AS First,
        LastName AS Last
INTO Person.NewContact
FROM Person.Person;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
SELECT ContactNum, First, Last FROM Person.NewContact;
GO


© 2015 Microsoft
Export (0) Print
Expand All

SQL_VARIANT_PROPERTY (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the base data type and other information about a sql_variant value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SQL_VARIANT_PROPERTY ( expression , property )

expression

Is an expression of type sql_variant.

property

Contains the name of the sql_variant property for which information is to be provided. property is varchar(128), and can be any one of the following values.

Value

Description

Base type of sql_variant returned

BaseType                 

SQL Server data type, such as:

bigint

binary

char

date

datetime

datetime2

datetimeoffset

decimal

float

int

money

nchar

numeric

nvarchar

real

smalldatetime

smallint

smallmoney

time

tinyint

uniqueidentifier

varbinary

varchar

sysname

NULL = Input is not valid.

Precision                 

Number of digits of the numeric base data type:

datetime = 23

smalldatetime = 16

float = 53

real = 24

decimal (p,s) and numeric (p,s) = p

money = 19

smallmoney = 10

bigint = 19

int = 10

smallint = 5

tinyint = 3

bit = 1

All other types = 0

int

NULL = Input is not valid.

Scale                 

Number of digits to the right of the decimal point of the numeric base data type:

decimal (p,s) and numeric (p,s) = s

money and smallmoney = 4

datetime = 3

all other types = 0

int

NULL = Input is not valid.

TotalBytes                 

Number of bytes required to hold both the metadata and data of the value. This information would be useful in checking the maximum side of data in a sql_variant column. If the value is larger than 900, index creation will fail.

int

NULL = Input is not valid.

Collation                 

Represents the collation of the particular sql_variant value.

sysname

NULL = Input is not valid.

MaxLength                 

Maximum data type length, in bytes. For example, MaxLength of nvarchar(50) is 100, MaxLength of int is 4.

int

NULL = Input is not valid.

sql_variant

The following example retrieves SQL_VARIANT_PROPERTY information about the colA value 46279.1 where colB =1689, given that tableA has colA that is of type sql_variant and colB.

CREATE   TABLE tableA(colA sql_variant, colB int)
INSERT INTO tableA values ( cast (46279.1 as decimal(8,2)), 1689)
SELECT   SQL_VARIANT_PROPERTY(colA,'BaseType') AS 'Base Type',
         SQL_VARIANT_PROPERTY(colA,'Precision') AS 'Precision',
         SQL_VARIANT_PROPERTY(colA,'Scale') AS 'Scale'
FROM      tableA
WHERE      colB = 1689

Here is the result set. Note that each of these three values is a sql_variant.

Base Type    Precision    Scale
---------    ---------    -----
decimal      8           2

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All
© 2015 Microsoft
Export (0) Print
Expand All

CAST and CONVERT (Transact-SQL)

 

Applies To: Azure SQL Database, SQL Data Warehouse, SQL Server 2008 - 2016 CTP3

Converts an expression of one data type to another in SQL Server 2016.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression

Is any valid expression.

data_type

Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length

Is an optional integer that specifies the length of the target data type. The default value is 30.

style

Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

Returns expression translated to data_type.

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. . Beginning with SQL Server 2012, the only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. All other conversion styles return error 9809.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Without century (yy) (1)

With century (yyyy)

Standard

Input/Output (3)

-

0 or 100 (1, 2)

Default for datetime and smalldatetime

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

1 = mm/dd/yy

101 = mm/dd/yyyy

2

102

ANSI

2 = yy.mm.dd

102 = yyyy.mm.dd

3

103

British/French

3 = dd/mm/yy

103 = dd/mm/yyyy

4

104

German

4 = dd.mm.yy

104 = dd.mm.yyyy

5

105

Italian

5 = dd-mm-yy

105 = dd-mm-yyyy

6

106 (1)

-

6 = dd mon yy

106 = dd mon yyyy

7

107 (1)

-

7 = Mon dd, yy

107 = Mon dd, yyyy

8

108

-

hh:mi:ss

-

9 or 109 (1, 2)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

10 = mm-dd-yy

110 = mm-dd-yyyy

11

111

JAPAN

11 = yy/mm/dd

111 = yyyy/mm/dd

12

112

ISO

12 = yymmdd

112 = yyyymmdd

-

13 or 113 (1, 2)

Europe default + milliseconds

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (2)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (2)

ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126 (4)

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

System_CAPS_noteNote

When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.

-

127(6, 7)

ISO8601 with time zone Z.

yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)

System_CAPS_noteNote

When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.

-

130 (1, 2)

Hijri (5)

dd mon yyyy hh:mi:ss:mmmAM

In this style, mon represents a multi-token Hijri unicode representation of the full month's name. This value will not render correctly on a default US installation of SSMS.

-

131 (2)

Hijri (5)

dd/mm/yyyy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

System_CAPS_importantImportant

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.

When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

Value

Output

0 (default)

A maximum of 6 digits. Use in scientific notation, when appropriate.

1

Always 8 digits. Always use in scientific notation.

2

Always 16 digits. Always use in scientific notation.

126, 128, 129

Included for legacy reasons and might be deprecated in a future release.

When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value

Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

126

Equivalent to style 2 when converting to char(n) or varchar(n)

When expression is xml, style can be one of the values shown in the following table. Other values are processed as 0.

Value

Output

0 (default)

Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset.

System_CAPS_noteNote

When you convert to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0. For more information, see Create Instances of XML Data.

1

Preserve insignificant white space. This style setting sets the default xml:space handling to behave the same as if xml:space="preserve" has been specified instead.

2

Enable limited internal DTD subset processing.

If enabled, the server can use the following information that is provided in an internal DTD subset to perform nonvalidating parse operations.

  • Defaults for attributes are applied.

  • Internal entity references are resolved and expanded.

  • The DTD content model will be checked for syntactical correctness.

The parser will ignore external DTD subsets. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.

3

Preserve insignificant white space and enable limited internal DTD subset processing.

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

Value

Output

0 (default)

Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1.

If the data_type is a binary type, the characters 0x are added to the left of the result.

1, 2

If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised.

If the length of the converted expression is greater than the length of the data_type the result will be right truncated.

Fixed length data_types that are larger then the converted result will have zeros added to the right of the result.

If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated.

If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits.

The characters 0x will be added to the left of the converted result for style 1.

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

System_CAPS_tipTip

This chart is available as a downloadable PDF file at the Microsoft Download Center.

Data type conversion table

When you convert between datetimeoffset and the character types char, varchar, nchar, and nvarchar the converted time zone offset part should always be double digits for both HH and MM for example, -08:00.

System_CAPS_noteNote

Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Large-value data types exhibit the same implicit and explicit conversion behavior as their smaller counterparts, specifically the varchar, nvarchar and varbinary data types. However, you should consider the following guidelines:

  • Conversion from image to varbinary(max) and vice-versa is an implicit conversion, and so are conversions between text and varchar(max), and ntext and nvarchar(max).

  • Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.

  • Conversion from varchar, nvarchar, or varbinary to their corresponding large-value data types is performed implicitly.

  • Conversion from the sql_variant data type to the large-value data types is an explicit conversion.

  • Large-value data types cannot be converted to the sql_variant data type.

For more information about how to convert from the xml data type, see Create Instances of XML Data.

When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a set of rules. For information about these rules, see Define the Serialization of XML Data. For information about how to convert from other data types to the xml data type, see Create Instances of XML Data.

Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

From data type

To data type

Result

int, smallint, or tinyint

char

*

 

varchar

*

 

nchar

E

 

nvarchar

E

money, smallmoney, numeric, decimal, float, or real

char

E

 

varchar

E

 

nchar

E

 

nvarchar

E

* = Result length too short to display. E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from version to version. The following example shows such a roundtrip conversion:

DECLARE @myval decimal (5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5));
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval));
System_CAPS_noteNote

Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS [Sick Leave]
FROM HumanResources.Employee e JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID >5;

Here is the result set.

FirstName   LastName      Title   Sick Leave

---------   ------------- ------- --------

Ken         Sanchez       NULL   *

Terri       Duffy         NULL   *

Roberto     Tamburello    NULL   *

Rob         Walters       NULL   *

Gail        Erickson      Ms.    *

(5 row(s) affected)

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.

From

To

Behavior

numeric

numeric

Round

numeric

int

Truncate

numeric

money

Round

money

int

Round

money

numeric

Round

float

int

Truncate

float

numeric

Round

Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

float

datetime

Round

datetime

int

Round

For example, the result of the following conversion is 10:

SELECT CAST(10.6496 AS int);

When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:

SELECT CAST(10.3496847 AS money);

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

The following table lists the styles for which the string-to-datetime conversion is nondeterministic.

All styles below 1001

106

107

109

113

130

1 With the exception of styles 20 and 21

Beginning in SQL Server 2012, if you use supplementary character (SC) collations, a CAST operation from nchar or nvarchar to an nchar or nvarchar type of smaller length will not truncate inside a surrogate pair; it truncates before the supplementary character. For example, the following code fragment leaves @x holding just 'ab'. There is not enough space to hold the supplementary character.

DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST (@x AS NVARCHAR(3));

When using SC collations the behavior of CONVERT, is analogous to that of CAST.

In earlier versions of SQL Server, the default style for CAST and CONVERT operations on time and datetime2 data types is 121 except when either type is used in a computed column expression. For computed columns, the default style is 0. This behavior impacts computed columns when they are created, used in queries involving auto-parameterization, or used in constraint definitions.

Under compatibility level 110 and higher, the default style for CAST and CONVERT operations on time and datetime2 data types is always 121. If your query relies on the old behavior, use a compatibility level less than 110, or explicitly specify the 0 style in the affected query.

Upgrading the database to compatibility level 110 and higher will not change user data that has been stored to disk. You must manually correct this data as appropriate. For example, if you used SELECT INTO to create a table from a source that contained a computed column expression described above, the data (using style 0) would be stored rather than the computed column definition itself. You would need to manually update this data to match style 121.

Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.

-- Use CAST
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

The following example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). This result is converted to an int data type after being rounded to the nearest whole number.

USE AdventureWorks2012;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Here is the result set.

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

The following example concatenates noncharacter, nonbinary expressions by using CAST.

USE AdventureWorks2012;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

Here is the result set.

ListPrice

------------------

The list price is 357.06

The list price is 364.09

The list price is 364.09

The list price is 364.09

The list price is 364.09

(5 row(s) affected)

The following example uses CAST in the select list to convert the Name column to a char(10) column.

USE AdventureWorks2012;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail AS s 
JOIN Production.Product AS p 
    ON s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Here is the result set.

Name       UnitPrice

---------- ---------------------

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

The following example converts the money column SalesYTD to an int and then to a char(20) column so that it can be used with the LIKE clause.

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person AS p 
JOIN Sales.SalesPerson AS s 
    ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

Here is the result set.

FirstName        LastName            SalesYTD         SalesPersonID

---------------- ------------------- ---------------- -------------

Tsvi             Reiter              2811012.7151      279

Syed             Abbas               219088.8836       288

Rachel           Valdez              2241204.0424      289

(3 row(s) affected)

The following are several examples that show using CONVERT to convert to typed XML by using the XML Data Type and Columns (SQL Server).

This example converts a string with white space, text and markup into typed XML and removes all insignificant white space (boundary white space between nodes):

CONVERT(XML, '<root><child/></root>')

This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

CONVERT(XML, '<root>          <child/>         </root>', 1)

This example casts a string with white space, text, and markup into typed XML:

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

For more examples, see Create Instances of XML Data.

The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

Here is the result set.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

The following example is approximately the opposite of the previous example. The example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

SELECT 
   '2006-04-25T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

 

(1 row(s) affected)

The following examples show the results of converting binary and character data by using different styles.

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];

Here is the result set.

Style 0, binary to character

----------------------------

Name

(1 row(s) affected)

--The following example shows how Style 1 can force the result
--to be truncated. The truncation is caused by
--including the characters 0x in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS [Style 1, binary to character];

Here is the result set.

Style 1, binary to character

------------------------------

0x4E616D

(1 row(s) affected)

--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in
--the result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS [Style 2, binary to character];

Here is the result set.

Style 2, binary to character

------------------------------

4E616D65

(1 row(s) affected)

--Convert the character value 'Name' to a binary value.
SELECT CONVERT(binary(8), 'Name', 0) AS [Style 0, character to binary];

Here is the result set.

Style 0, character to binary

----------------------------------

0x4E616D6500000000

(1 row(s) affected)

SELECT CONVERT(binary(4), '0x4E616D65', 1) AS [Style 1, character to binary];

Here is the result set.

Style 1, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

SELECT CONVERT(binary(4), '4E616D65', 2) AS [Style 2, character to binary];

Here is the result set.

Style 2, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

The following example demonstrates the conversion of date, time, and datetime data types.

DECLARE @d1 date, @t1 time, @dt1 datetime;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero 
-- which converts to January 1, 1900.
SELECT @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [datetime], CAST (@dt1 AS date) AS [datetime as date], CAST (@dt1 AS time) AS [datetime as time];
© 2015 Microsoft
Export (0) Print
Expand All

PARSE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the result of an expression, translated to the requested data type in SQL Server.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

PARSE ( string_value AS data_type [ USING culture ] )

string_value

nvarchar(4000) value representing the formatted value to parse into the specified data type.

string_value must be a valid representation of the requested data type, or PARSE raises an error.

data_type

Literal value representing the data type requested for the result.

culture

Optional string that identifies the culture in which string_value is formatted.

If the culture argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, PARSE raises an error.

Returns the result of the expression, translated to the requested data type.

Null values passed as arguments to PARSE are treated in two ways:

  1. If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner.

  2. If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch.

Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

More information about the data_type parameter

The values for the data_type parameter are restricted to the types shown in the following table, together with styles. The style information is provided to help determine what types of patterns are allowed. For more information on styles, see the .NET Framework documentation for the System.Globalization.NumberStyles and DateTimeStyles enumerations.

Category

Type

.NET Framework type

Styles used

Numeric

bigint

Int64

NumberStyles.Number

Numeric

int

Int32

NumberStyles.Number

Numeric

smallint

Int16

NumberStyles.Number

Numeric

tinyint

Byte

NumberStyles.Number

Numeric

decimal

Decimal

NumberStyles.Number

Numeric

numeric

Decimal

NumberStyles.Number

Numeric

float

Double

NumberStyles.Float

Numeric

real

Single

NumberStyles.Float

Numeric

smallmoney

Decimal

NumberStyles.Currency

Numeric

money

Decimal

NumberStyles.Currency

Date and Time

date

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

time

TimeSpan

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetime

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

smalldatetime

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetime2

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetimeoffset

DateTimeOffset

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

More information about the culture parameter

The following table shows the mappings from SQL Server languages to .NET Framework cultures.

Full name

Alias

LCID

Specific culture

us_english

English

1033

en-US

Deutsch

German

1031

de-DE

Français

French

1036

fr-FR

日本語

Japanese

1041

ja-JP

Dansk

Danish

1030

da-DK

Español

Spanish

3082

es-ES

Italiano

Italian

1040

it-IT

Nederlands

Dutch

1043

nl-NL

Norsk

Norwegian

2068

nn-NO

Português

Portuguese

2070

pt-PT

Suomi

Finnish

1035

fi

Svenska

Swedish

1053

sv-SE

čeština

Czech

1029

Cs-CZ

magyar

Hungarian

1038

Hu-HU

polski

Polish

1045

Pl-PL

română

Romanian

1048

Ro-RO

hrvatski

Croatian

1050

hr-HR

slovenčina

Slovak

1051

Sk-SK

slovenski

Slovenian

1060

Sl-SI

ελληνικά

Greek

1032

El-GR

български

Bulgarian

1026

bg-BG

русский

Russian

1049

Ru-RU

Türkçe

Turkish

1055

Tr-TR

British

British English

2057

en-GB

eesti

Estonian

1061

Et-EE

latviešu

Latvian

1062

lv-LV

lietuvių

Lithuanian

1063

lt-LT

Português (Brasil)

Brazilian

1046

pt-BR

繁體中文

Traditional Chinese

1028

zh-TW

한국어

Korean

1042

Ko-KR

简体中文

Simplified Chinese

2052

zh-CN

Arabic

Arabic

1025

ar-SA

ไทย

Thai

1054

Th-TH

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;

Here is the result set.

Result
---------------
2010-12-13 00:00:00.0000000

(1 row(s) affected)

SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result;

Here is the result set.

Result
---------------
345.98

(1 row(s) affected)

-- The English language is mapped to en-US specific culture
SET LANGUAGE 'English';
SELECT PARSE('12/16/2010' AS datetime2) AS Result;

Here is the result set.

Result
---------------
2010-12-16 00:00:00.0000000

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

TRY_CAST (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Applies to: SQL Server (SQL Server 2012 through current version) , Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

TRY_CAST ( expression AS data_type [ ( length ) ] )

expression

The value to be cast. Any valid expression.

data_type

The data type into which to cast expression.

length

Optional integer that specifies the length of the target data type.

The range of acceptable values is determined by the value of data_type.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CAST takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

TRY_CAST is not a new reserved keyword and is available in all compatibility levels. TRY_CAST has the same semantics as TRY_CONVERT when connecting to remote servers.

The following example demonstrates that TRY_CAST returns null when the cast fails.

SELECT 
    CASE WHEN TRY_CAST('test' AS float) IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Here is the result set.

Result
------------
Cast failed

(1 row(s) affected)

The following example demonstrates that the expression must be in the expected format.

SET DATEFORMAT dmy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Here is the result set.

Result
----------------------
NULL

(1 row(s) affected)

The following example demonstrates that TRY_CAST returns an error when the cast is explicitly not permitted.

SELECT TRY_CAST(4 AS xml) AS Result;
GO

The result of this statement is an error, because an integer cannot be cast into an xml data type.

Explicit conversion from data type int to xml is not allowed.

This example demonstrates that the expression must be in the expected format.

SET DATEFORMAT mdy;
SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;
GO

Here is the result set.

Result
----------------------------------
2010-12-31 00:00:00.0000000

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

TRY_CONVERT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version)

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ]

The data type into which to cast expression.

expression

The value to be cast.

style

Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT (Transact-SQL).

The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

TRY_CONVERT is a reserved keyword in compatibility level 110 and higher.

This function is capable of being remoted to servers that have a version of SQL Server 2012 and above. It will not be remoted to servers that have a version below SQL Server 2012.

The following example demonstrates that TRY_CONVERT returns null when the cast fails.

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
GO

Here is the result set.

Result
------------
Cast failed

(1 row(s) affected)

The following example demonstrates that the expression must be in the expected format.

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Here is the result set.

Result
----------------------
NULL

(1 row(s) affected)

The following example demonstrates that TRY_CONVERT returns an error when the cast is explicitly not permitted.

SELECT TRY_CONVERT(xml, 4) AS Result;
GO

The result of this statement is an error, because an integer cannot be cast into an xml data type.

Explicit conversion from data type int to xml is not allowed.

This example demonstrates that the expression must be in the expected format.

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Here is the result set.

Result
----------------------------------
2010-12-31 00:00:00.0000000

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

TRY_PARSE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

TRY_PARSE ( string_value AS data_type [ USING culture ] )

string_value

nvarchar(4000) value representing the formatted value to parse into the specified data type.

string_value must be a valid representation of the requested data type, or TRY_PARSE returns null.

data_type

Literal representing the data type requested for the result.

culture

Optional string that identifies the culture in which string_value is formatted.

If the culture argument is not provided, the language of the current session is used. This language is set either implicitly or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, PARSE raises an error.

Returns the result of the expression, translated to the requested data type, or null if the cast fails.

Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

More information about the data_type parameter

The values for the data_type parameter are restricted to the types shown in the following table, together with styles. The style information is provided to help determine what types of patterns are allowed. For more information on styles, see the .NET Framework documentation for the System.Globalization.NumberStyles and DateTimeStyles enumerations.

Category

Type

.NET type

Styles used

Numeric

bigint

Int64

NumberStyles.Number

Numeric

int

Int32

NumberStyles.Number

Numeric

smallint

Int16

NumberStyles.Number

Numeric

tinyint

Byte

NumberStyles.Number

Numeric

decimal

Decimal

NumberStyles.Number

Numeric

numeric

Decimal

NumberStyles.Number

Numeric

float

Double

NumberStyles.Float

Numeric

real

Single

NumberStyles.Float

Numeric

smallmoney

Decimal

NumberStyles.Currency

Numeric

money

Decimal

NumberStyles.Currency

Date and Time

date

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

time

TimeSpan

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetime

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

smalldatetime

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetime2

DateTime

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

Date and Time

datetimeoffset

DateTimeOffset

DateTimeStyles.AllowWhiteSpaces | DateTimeStyles.AssumeUniversal

More information about the culture parameter

The following table shows the mappings from SQL Server languages to .NET Framework cultures.

Full name

Alias

LCID

Specific culture

us_english

English

1033

en-US

Deutsch

German

1031

de-DE

Français

French

1036

fr-FR

日本語

Japanese

1041

ja-JP

Dansk

Danish

1030

da-DK

Español

Spanish

3082

es-ES

Italiano

Italian

1040

it-IT

Nederlands

Dutch

1043

nl-NL

Norsk

Norwegian

2068

nn-NO

Português

Portuguese

2070

pt-PT

Suomi

Finnish

1035

fi

Svenska

Swedish

1053

sv-SE

čeština

Czech

1029

Cs-CZ

magyar

Hungarian

1038

Hu-HU

polski

Polish

1045

Pl-PL

română

Romanian

1048

Ro-RO

hrvatski

Croatian

1050

hr-HR

slovenčina

Slovak

1051

Sk-SK

slovenski

Slovenian

1060

Sl-SI

ελληνικά

Greek

1032

El-GR

български

Bulgarian

1026

bg-BG

русский

Russian

1049

Ru-RU

Türkçe

Turkish

1055

Tr-TR

British

British English

2057

en-GB

eesti

Estonian

1061

Et-EE

latviešu

Latvian

1062

lv-LV

lietuvių

Lithuanian

1063

lt-LT

Português (Brasil)

Brazilian

1046

pt-BR

繁體中文

Traditional Chinese

1028

zh-TW

한국어

Korean

1042

Ko-KR

简体中文

Simplified Chinese

2052

zh-CN

Arabic

Arabic

1025

ar-SA

ไทย

Thai

1054

Th-TH

SELECT TRY_PARSE('Jabberwokkie' AS datetime2 USING 'en-US') AS Result;

Here is the result set.

Result
---------------
NULL

(1 row(s) affected)

SELECT
    CASE WHEN TRY_PARSE('Aragorn' AS decimal USING 'sr-Latn-CS') IS NULL
        THEN 'True'
        ELSE 'False'
END
AS Result;

Here is the result set.

Result
---------------
True

(1 row(s) affected)

SET LANGUAGE English;
SELECT IIF(TRY_PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result;

Here is the result set.

Result
---------------
False

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

Aggregate Functions (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Aggregate functions perform a calculation on a set of values and return a single value. Except for COUNT, aggregate functions ignore null values. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic. This means aggregate functions return the same value any time that they are called by using a specific set of input values. For more information about function determinism, see Deterministic and Nondeterministic Functions. The OVER clause may follow all aggregate functions except GROUPING and GROUPING_ID.

Aggregate functions can be used as expressions only in the following:

  • The select list of a SELECT statement (either a subquery or an outer query).

  • A HAVING clause.

Transact-SQL provides the following aggregate functions:

AVG           

MIN           

CHECKSUM_AGG           

SUM           

COUNT           

STDEV           

COUNT_BIG           

STDEVP           

GROUPING           

VAR           

GROUPING_ID           

VARP           

MAX

© 2015 Microsoft
Export (0) Print
Expand All

AVG (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the average of the values in a group. Null values are ignored.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

AVG ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )  

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.

expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

The return type is determined by the type of the evaluated result of expression.

Expression result

Return type

tinyint

int

smallint

int

int

int

bigint

bigint

decimal category (p, s)

decimal(38, s) divided by decimal(10, 0)

money and smallmoney category

money

float and real category

float

If the data type of expression is an alias data type, the return type is also of the alias data type. However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value is of the promoted data type and not the alias data type.

AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values. If the sum exceeds the maximum value for the data type of the return value an error will be returned.

AVG is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example calculates the average vacation hours and the sum of sick leave hours that the vice presidents of Adventure Works Cycles have used. Each of these aggregate functions produces a single summary value for all the retrieved rows. The example uses the AdventureWorks2012 database.

SELECT AVG(VacationHours)AS 'Average vacation hours', 
    SUM(SickLeaveHours) AS 'Total sick leave hours'
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Vice President%';

Here is the result set.

Average vacation hours       Total sick leave hours

----------------------       ----------------------

25                           97

(1 row(s) affected)

When used with a GROUP BY clause, each aggregate function produces a single value for each group, instead of for the whole table. The following example produces summary values for each sales territoryin the AdventureWorks2012 database. The summary lists the average bonus received by the sales people in each territory and the sum of year-to-date sales for each territory.

SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales'
FROM Sales.SalesPerson
GROUP BY TerritoryID;
GO

Here is the result set.

TerritoryID Average Bonus         YTD Sales
----------- --------------------- ---------------------
NULL        0.00                  1252127.9471
1           4133.3333             4502152.2674
2           4100.00               3763178.1787
3           2500.00               3189418.3662
4           2775.00               6709904.1666
5           6700.00               2315185.611
6           2750.00               4058260.1825
7           985.00                3121616.3202
8           75.00                 1827066.7118
9           5650.00               1421810.9242
10          5150.00               4116871.2277

(11 row(s) affected)

The following statement returns the average list price of productsin the AdventureWorks2012 database. By specifying DISTINCT, only unique values are considered in the calculation.

SELECT AVG(DISTINCT ListPrice)
FROM Production.Product;

Here is the result set.

------------------------------

437.4042

(1 row(s) affected)

Without DISTINCT, the AVG function finds the average list price of all products in the Product tablein the AdventureWorks2012 database including any duplicate values.

SELECT AVG(ListPrice)
FROM Production.Product;

Here is the result set.

------------------------------

438.6662

(1 row(s) affected)

The following example uses the AVG function with the OVER clause to provide a moving average of yearly sales for each territory in the Sales.SalesPerson table in the AdventureWorks2012 database. The data is partitioned by TerritoryID and logically ordered by SalesYTD. This means that the AVG function is computed for each territory based on the sales year. Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                           ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

In this example, the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

CHECKSUM_AGG (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the checksum of the values in a group. Null values are ignored. Can be followed by the OVER clause.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that CHECKSUM_AGG returns the checksum of unique values.

expression

Is an integer expression. Aggregate functions and subqueries are not allowed.

Returns the checksum of all expression values as int.

CHECKSUM_AGG can be used to detect changes in a table.

The order of the rows in the table does not affect the result of CHECKSUM_AGG. Also, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the GROUP BY clause.

If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.

CHECKSUM_AGG has similar functionality with other aggregate functions. For more information, see Aggregate Functions (Transact-SQL).

The following example uses CHECKSUM_AGG to detect changes in the Quantity column of the ProductInventory table in the AdventureWorks2012 database.

--Get the checksum value before the column value is changed.
SELECT CHECKSUM_AGG(CAST(Quantity AS int))
FROM Production.ProductInventory;
GO

Here is the result set.

------------------------
262
UPDATE Production.ProductInventory 
SET Quantity=125
WHERE Quantity=100;
GO
--Get the checksum of the modified column.
SELECT CHECKSUM_AGG(CAST(Quantity AS int))
FROM Production.ProductInventory;

Here is the result set.

------------------------
287
© 2015 Microsoft
Export (0) Print
Expand All

COUNT (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) 
    OVER ( [ partition_by_clause ] order_by_clause )

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that COUNT returns the number of unique nonnull values.

expression

Is an expression of any type except text, image, or ntext. Aggregate functions and subqueries are not permitted.

*

Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead.

COUNT is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example lists the number of different titles that an employee who works at Adventure Works Cycles can hold.

SELECT COUNT(DISTINCT Title)
FROM HumanResources.Employee;
GO

Here is the result set.

-----------

67

(1 row(s) affected)

The following example finds the total number of employees who work at Adventure Works Cycles.

SELECT COUNT(*)
FROM HumanResources.Employee;
GO

Here is the result set.

            

-----------

290

(1 row(s) affected)

The following example shows that COUNT(*) can be combined with other aggregate functions in the select list. The example uses the AdventureWorks2012 database.

SELECT COUNT(*), AVG(Bonus)
FROM Sales.SalesPerson
WHERE SalesQuota > 25000;
GO

Here is the result set.

                                 

----------- ---------------------

14 3472.1428

(1 row(s) affected)

The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table in the AdventureWorks2012 database.

SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Here is the result set.

Name                          MinSalary             MaxSalary             AvgSalary             EmployeesPerDept
----------------------------- --------------------- --------------------- --------------------- ----------------
Document Control              10.25                 17.7885               14.3884               5
Engineering                   32.6923               63.4615               40.1442               6
Executive                     39.06                 125.50                68.3034               4
Facilities and Maintenance    9.25                  24.0385               13.0316               7
Finance                       13.4615               43.2692               23.935                10
Human Resources               13.9423               27.1394               18.0248               6
Information Services          27.4038               50.4808               34.1586               10
Marketing                     13.4615               37.50                 18.4318               11
Production                    6.50                  84.1346               13.5537               195
Production Control            8.62                  24.5192               16.7746               8
Purchasing                    9.86                  30.00                 18.0202               14
Quality Assurance             10.5769               28.8462               15.4647               6
Research and Development      40.8654               50.4808               43.6731               4
Sales                         23.0769               72.1154               29.9719               18
Shipping and Receiving        9.00                  19.2308               10.8718               6
Tool Design                   8.62                  29.8462               23.5054               6

 (16 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

COUNT_BIG (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the number of items in a group. COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )
   OVER ( [ partition_by_clause ] order_by_clause )

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that COUNT_BIG returns the number of unique nonnull values.

expression

Is an expression of any type. Aggregate functions and subqueries are not permitted.

*

Specifies that all rows should be counted to return the total number of rows in a table. COUNT_BIG(*) takes no parameters and cannot be used with DISTINCT. COUNT_BIG(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT_BIG(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that AVG be performed only on each unique instance of a value, regardless of how many times the value occurs.

expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT_BIG (ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT_BIG (DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

COUNT_BIG is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

© 2015 Microsoft
Export (0) Print
Expand All

GROUPING (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

GROUPING ( <column_expression> )

<column_expression>

Is a column or an expression that contains a column in a GROUP BY clause.

GROUPING is used to distinguish the null values that are returned by ROLLUP, CUBE or GROUPING SETS from standard null values. The NULL returned as the result of a ROLLUP, CUBE or GROUPING SETS operation is a special use of NULL. This acts as a column placeholder in the result set and means all.

The following example groups SalesQuota and aggregates SaleYTD amounts in the AdventureWorks2012 database. The GROUPING function is applied to the SalesQuota column.

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO

The result set shows two null values under SalesQuota. The first NULL represents the group of null values from this column in the table. The second NULL is in the summary row added by the ROLLUP operation. The summary row shows the TotalSalesYTD amounts for all SalesQuota groups and is indicated by 1 in the Grouping column.

Here is the result set.

SalesQuota     TotalSalesYTD       Grouping

------------   -----------------   --------

NULL           1533087.5999          0

250000.00      33461260.59           0

300000.00      9299677.9445          0

NULL           44294026.1344         1

(4 row(s) affected)

© 2015 Microsoft
Export (0) Print
Expand All

GROUPING_ID (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

GROUPING_ID ( <column_expression> [ ,...n ] )

<column_expression>

Is a column_expression in a GROUP BY clause.

The GROUPING_ID <column_expression> must exactly match the expression in the GROUP BY list. For example, if you are grouping by DATEPART (yyyy, <column name>), use GROUPING_ID (DATEPART (yyyy, <column name>)); or if you are grouping by <column name>, use GROUPING_ID (<column name>).

GROUPING_ID (<column_expression> [ ,...n ]) inputs the equivalent of the GROUPING (<column_expression>) return for each column in its column list in each output row as a string of ones and zeros. GROUPING_ID interprets that string as a base-2 number and returns the equivalent integer. For example consider the following statement: SELECT a, b, c, SUM(d), GROUPING_ID(a,b,c) FROM T GROUP BY <group by list>. The following table shows the GROUPING_ID () input and output values.

Columns aggregated

GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING_ID () output

a

100

4

b

010

2

c

001

1

ab

110

6

ac

101

5

bc

011

3

abc

111

7

Each GROUPING_ID argument must be an element of the GROUP BY list. GROUPING_ID () returns an integer bitmap whose lowest N bits may be lit. A lit bit indicates the corresponding argument is not a grouping column for the given output row. The lowest-order bit corresponds to argument N, and the N-1th lowest-order bit corresponds to argument 1.

For a single grouping query, GROUPING (<column_expression>) is equivalent to GROUPING_ID (<column_expression>), and both return 0.

For example, the following statements are equivalent:

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,B) 
SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A,B

The following example returns the count of employees by Name and Title, Name, and company total in the AdventureWorks2012 database. GROUPING_ID() is used to create a value for each row in the Title column that identifies its level of aggregation.

SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Job Title'
    ,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.BusinessEntityID = DH.BusinessEntityID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle);

In the following code, to return only the rows that have a count of employees by title, remove the comment characters from HAVING GROUPING_ID(D.Name, E.JobTitle); = 0 in the AdventureWorks2012 database. To return only rows with a count of employees by department, remove the comment characters from HAVING GROUPING_ID(D.Name, E.JobTitle) = 1;.

SELECT D.Name
    ,E.JobTitle
    ,GROUPING_ID(D.Name, E.JobTitle) AS 'Grouping Level'
    ,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee AS E
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
        ON E.BusinessEntityID = DH.BusinessEntityID
    INNER JOIN HumanResources.Department AS D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle)
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; --Group by Name;

Here is the unfiltered result set.

Name

Title

Grouping Level

Employee Count

Name

Document Control

Control Specialist

0

2

Document Control

Document Control

Document Control Assistant

0

2

Document Control

Document Control

Document Control Manager

0

1

Document Control

Document Control

NULL

1

5

Document Control

Facilities and Maintenance

Facilities Administrative Assistant

0

1

Facilities and Maintenance

Facilities and Maintenance

Facilities Manager

0

1

Facilities and Maintenance

Facilities and Maintenance

Janitor

0

4

Facilities and Maintenance

Facilities and Maintenance

Maintenance Supervisor

0

1

Facilities and Maintenance

Facilities and Maintenance

NULL

1

7

Facilities and Maintenance

NULL

NULL

3

12

NULL

The following example uses GROUPING_ID() to filter a result set that contains multiple grouping levels by grouping level. Similar code can be used to create a view that has several grouping levels and a stored procedure that calls the view by passing a parameter that filters the view by grouping level. The example uses the AdventureWorks2012 database.

DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total'             THEN 15
        WHEN N'SalesPerson Total'       THEN 14
        WHEN N'Store Total'             THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total'             THEN 7
        ELSE N'Unknown'
    END);

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N'Store'
    ,(SELECT P.FirstName + ' ' + P.LastName 
        FROM Person.Person AS P 
        WHERE P.BusinessEntityID = H.SalesPersonID)
        AS N'Sales Person'
    ,SUM(TotalDue)AS N'TotalSold'
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N'GROUPING base-2'
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N'GROUPING_ID'
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N'Grand Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N'SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N'Store Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N'Group Total'
        ELSE N'Error'
        END AS N'Level'
FROM Sales.Customer AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
HAVING GROUPING_ID(
    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

The code in the following examples show using GROUPING() to compute the Bit Vector(base-2) column. GROUPING_ID() is used to compute the corresponding Integer Equivalent column. The column order in the GROUPING_ID() function is the opposite of the column order of the columns that are concatenated by the GROUPING() function.

In these examples, GROUPING_ID() is used to create a value for each row in the Grouping Level column to identify the level of grouping. Grouping levels are not always a consecutive list of integers that start with 1 (0, 1, 2,...n).

System_CAPS_noteNote

GROUPING and GROUPING_ID can be used n a HAVING clause to filter a result set.

In this example, all grouping levels do not appear as they do in the following CUBE example. If the order of the columns in the ROLLUP list is changed, the level values in the Grouping Level column will also have to be changed. The example uses the AdventureWorks2012 database.

SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
     AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2007',N'2008')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

Here is a partial result set.

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2007

1

1

1497452.6066

000

0

Year Month Day

2007

1

2

21772.3494

000

0

Year Month Day

2007

2

1

2705653.5913

000

0

Year Month Day

2007

2

2

21684.4068

000

0

Year Month Day

2008

1

1

1908122.0967

000

0

Year Month Day

2008

1

2

46458.0691

000

0

Year Month Day

2008

2

1

3108771.9729

000

0

Year Month Day

2008

2

2

54598.5488

000

0

Year Month Day

2007

1

NULL

1519224.956

100

1

Year Month

2007

2

NULL

2727337.9981

100

1

Year Month

2008

1

NULL

1954580.1658

100

1

Year Month

2008

2

NULL

3163370.5217

100

1

Year Month

2007

NULL

NULL

4246562.9541

110

3

Year

2008

NULL

NULL

5117950.6875

110

3

Year

NULL

NULL

NULL

9364513.6416

111

7

Grand Total

In this example, the GROUPING_ID() function is used to create a value for each row in the Grouping Level column to identify the level of grouping.

Unlike ROLLUP in the previous example, CUBE outputs all grouping levels. If the order of the columns in the CUBE list is changed, the level values in the Grouping Level column will also have to be changed. The example uses the AdventureWorks2012 database

SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
        AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2007',N'2008')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

Here is a partial result set.

Year

Month

Day

Total Due

Bit Vector (base-2)

Integer Equivalent

Grouping Level

2007

1

1

1497452.6066

000

0

Year Month Day

2007

1

2

21772.3494

000

0

Year Month Day

2007

2

1

2705653.5913

000

0

Year Month Day

2007

2

2

21684.4068

000

0

Year Month Day

2008

1

1

1908122.0967

000

0

Year Month Day

2008

1

2

46458.0691

000

0

Year Month Day

2008

2

1

3108771.9729

000

0

Year Month Day

2008

2

2

54598.5488

000

0

Year Month Day

2007

1

NULL

1519224.956

100

1

Year Month

2007

2

NULL

2727337.9981

100

1

Year Month

2008

1

NULL

1954580.1658

100

1

Year Month

2008

2

NULL

3163370.5217

100

1

Year Month

2007

NULL

1

4203106.1979

010

2

Year Day

2007

NULL

2

43456.7562

010

2

Year Day

2008

NULL

1

5016894.0696

010

2

Year Day

2008

NULL

2

101056.6179

010

2

Year Day

2007

NULL

NULL

4246562.9541

110

3

Year

2008

NULL

NULL

5117950.6875

110

3

Year

NULL

1

1

3405574.7033

001

4

Month Day

NULL

1

2

68230.4185

001

4

Month Day

NULL

2

1

5814425.5642

001

4

Month Day

NULL

2

2

76282.9556

001

4

Month Day

NULL

1

NULL

3473805.1218

101

5

Month

NULL

2

NULL

5890708.5198

101

5

Month

NULL

NULL

1

9220000.2675

011

6

Day

NULL

NULL

2

144513.3741

011

6

Day

NULL

NULL

NULL

9364513.6416

111

7

Grand Total

© 2015 Microsoft
Export (0) Print
Expand All

MAX (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the maximum value in the expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

MAX ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )   

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered. DISTINCT is not meaningful with MAX and is available for ISO compatibility only.

expression

Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MAX can be used with numeric, character, uniqueidentifier, and datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.

For more information, see Expressions (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

Returns a value same as expression.

MAX ignores any null values.

For character columns, MAX finds the highest value in the collating sequence.

MAX is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the highest (maximum) tax rate in the AdventureWorks2012 database.

SELECT MAX(TaxRate)
FROM Sales.SalesTaxRate;
GO

Here is the result set.

-------------------

19.60

Warning, null value eliminated from aggregate.

(1 row(s) affected)

The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table in the AdventureWorks2012 database.

SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Here is the result set.

Name                          MinSalary             MaxSalary             AvgSalary             EmployeesPerDept
----------------------------- --------------------- --------------------- --------------------- ----------------
Document Control              10.25                 17.7885               14.3884               5
Engineering                   32.6923               63.4615               40.1442               6
Executive                     39.06                 125.50                68.3034               4
Facilities and Maintenance    9.25                  24.0385               13.0316               7
Finance                       13.4615               43.2692               23.935                10
Human Resources               13.9423               27.1394               18.0248               6
Information Services          27.4038               50.4808               34.1586               10
Marketing                     13.4615               37.50                 18.4318               11
Production                    6.50                  84.1346               13.5537               195
Production Control            8.62                  24.5192               16.7746               8
Purchasing                    9.86                  30.00                 18.0202               14
Quality Assurance             10.5769               28.8462               15.4647               6
Research and Development      40.8654               50.4808               43.6731               4
Sales                         23.0769               72.1154               29.9719               18
Shipping and Receiving        9.00                  19.2308               10.8718               6
Tool Design                   8.62                  29.8462               23.5054               6

 (16 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

MIN (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the minimum value in the expression. May be followed by the OVER clause.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

MIN ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered. DISTINCT is not meaningful with MIN and is available for ISO compatibility only.

expression

Is a constant, column name, or function, and any combination of arithmetic, bitwise, and string operators. MIN can be used with numeric, char, varchar, uniqueidentifier, or datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.

For more information, see Expressions (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

Returns a value same as expression.

MIN ignores any null values.

With character data columns, MIN finds the value that is lowest in the sort sequence.

MIN is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the lowest (minimum) tax rate. The example uses the AdventureWorks2012 database

SELECT MIN(TaxRate)
FROM Sales.SalesTaxRate;
GO

Here is the result set.

-------------------

5.00

(1 row(s) affected)

The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department in the HumanResources.Department table in the AdventureWorks2012 database.

SELECT DISTINCT Name
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
       ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
     ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
 ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;

Here is the result set.

Name                          MinSalary             MaxSalary             AvgSalary             EmployeesPerDept
----------------------------- --------------------- --------------------- --------------------- ----------------
Document Control              10.25                 17.7885               14.3884               5
Engineering                   32.6923               63.4615               40.1442               6
Executive                     39.06                 125.50                68.3034               4
Facilities and Maintenance    9.25                  24.0385               13.0316               7
Finance                       13.4615               43.2692               23.935                10
Human Resources               13.9423               27.1394               18.0248               6
Information Services          27.4038               50.4808               34.1586               10
Marketing                     13.4615               37.50                 18.4318               11
Production                    6.50                  84.1346               13.5537               195
Production Control            8.62                  24.5192               16.7746               8
Purchasing                    9.86                  30.00                 18.0202               14
Quality Assurance             10.5769               28.8462               15.4647               6
Research and Development      40.8654               50.4808               43.6731               4
Sales                         23.0769               72.1154               29.9719               18
Shipping and Receiving        9.00                  19.2308               10.8718               6
Tool Design                   8.62                  29.8462               23.5054               6

 (16 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

STDEV (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the statistical standard deviation of all values in the specified expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

STDEV ( [ ALL | DISTINCT ] expression ) 
   OVER ( [ partition_by_clause ] order_by_clause )  

ALL

Applies the function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered.

expression

Is a numeric expression. Aggregate functions and subqueries are not permitted. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEV can be used with numeric columns only. Null values are ignored.

STDEV is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the standard deviation for all bonus values in the SalesPerson table in the AdventureWorks2012 database.

SELECT STDEV(Bonus)
FROM Sales.SalesPerson;
GO
© 2015 Microsoft
Export (0) Print
Expand All

STDEVP (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the statistical standard deviation for the population for all values in the specified expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

STDEVP ( [ ALL | DISTINCT ] expression ) 
   OVER ( [ partition_by_clause ] order_by_clause )  

ALL

Applies the function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered.

expression

Is a numeric expression. Aggregate functions and subqueries are not permitted. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

If STDEVP is used on all items in a SELECT statement, each value in the result set is included in the calculation. STDEVP can be used with numeric columns only. Null values are ignored.

STDEVP is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the standard deviation for the population for all bonus values in the SalesPerson table in the AdventureWorks2012 database.

SELECT STDEVP(Bonus)
FROM Sales.SalesPerson;
GO
© 2015 Microsoft
Export (0) Print
Expand All

SUM (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SUM ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )  

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that SUM return the sum of unique values.

expression

Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted. For more information, see Expressions (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

Returns the summation of all expression values in the most precise expression data type.

Expression result

Return type

tinyint

int

smallint

int

int

int

bigint

bigint

decimal category (p, s)

decimal(38, s)

money and smallmoney category

money

float and real category

float

SUM is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following examples shows using the SUM function to return summary data in the AdventureWorks2012 database.

SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL 
    AND ListPrice != 0.00 
    AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO

Here is the result set.

Color

--------------- --------------------- ---------------------

Black           27404.84              5214.9616

Silver          26462.84              14665.6792

White           19.00                 6.7926

(3 row(s) affected)

The following example uses the SUM function with the OVER clause to provide a cumulative total of yearly sales for each territory in the Sales.SalesPerson table in the AdventureWorks2012 database. The data is partitioned by TerritoryID and logically ordered by SalesYTD. This means that the SUM function is computed for each territory based on the sales year. Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The cumulative sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                           ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                            ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

(10 row(s) affected)

In this example, the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the SUM function is applied. The query returns a cumulative total of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

SELECT BusinessEntityID, TerritoryID 
   ,DATEPART(yy,ModifiedDate) AS SalesYear
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS MovingAvg
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) 
                                            ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;

Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93
(10 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

VAR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the statistical variance of all values in the specified expression. May be followed by the OVER clause.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

VAR ( [ ALL | DISTINCT ] expression ) 
   OVER ( [ partition_by_clause ] order_by_clause )  

ALL

Applies the function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered.

expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation. VAR can be used with numeric columns only. Null values are ignored.

VAR is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the variance for all bonus values in the SalesPerson table in the AdventureWorks2012 database.

SELECT VAR(Bonus)
FROM Sales.SalesPerson;
GO
© 2015 Microsoft
Export (0) Print
Expand All

VARP (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the statistical variance for the population for all values in the specified expression.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

VARP ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )  nh

ALL

Applies the function to all values. ALL is the default.

DISTINCT

Specifies that each unique value is considered.

expression

Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries are not permitted.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).

If VARP is used on all items in a SELECT statement, each value in the result set is included in the calculation. VARP can be used with numeric columns only. Null values are ignored.

VARP is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

The following example returns the variance for the population for all bonus values in the SalesPerson table in the AdventureWorks2012 database.

SELECT VARP(Bonus)
FROM Sales.SalesPerson;
GO
© 2015 Microsoft
Export (0) Print
Expand All

Date and Time Data Types and Functions (Transact-SQL)

 

Updated: December 2, 2015

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

The Transact-SQL date and time data types are listed in the following table. 

Data type

Format

Range

Accuracy

Storage size (bytes)

User-defined fractional second precision

Time zone offset

time

hh:mm:ss[.nnnnnnn]

00:00:00.0000000 through 23:59:59.9999999

100 nanoseconds

3 to 5

Yes

No

date

YYYY-MM-DD

0001-01-01 through 9999-12-31

1 day

3

No

No

smalldatetime

YYYY-MM-DD hh:mm:ss

1900-01-01 through 2079-06-06

1 minute

4

No

No

datetime

YYYY-MM-DD hh:mm:ss[.nnn]

1753-01-01 through 9999-12-31

0.00333 second

8

No

No

datetime2

YYYY-MM-DD hh:mm:ss[.nnnnnnn]

0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

100 nanoseconds

6 to 8

Yes

No

datetimeoffset

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)

100 nanoseconds

8 to 10

Yes

Yes

System_CAPS_noteNote

The Transact-SQL rowversion data type is not a date or time data type. timestamp is a deprecated synonym for rowversion.

The Transact-SQL date and time functions are listed in the following tables. For more information about determinism, see Deterministic and Nondeterministic Functions.

All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running.

SQL Server 2016 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

Function

Syntax

Return value

Return data type

Determinism

SYSDATETIME

SYSDATETIME ()

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

datetime2(7)

Nondeterministic

SYSDATETIMEOFFSET

SYSDATETIMEOFFSET ( )

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

datetimeoffset(7)

Nondeterministic

SYSUTCDATETIME

SYSUTCDATETIME ( )

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

datetime2(7)

Nondeterministic

Function

Syntax

Return value

Return data type

Determinism

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP

Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

datetime

Nondeterministic

GETDATE

GETDATE ( )

Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.

datetime

Nondeterministic

GETUTCDATE

GETUTCDATE ( )

Returns a datetime value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).

datetime

Nondeterministic

Function

Syntax

Return value

Return data type

Determinism

DATENAME

DATENAME ( datepart , date )

Returns a character string that represents the specified datepart of the specified date.

nvarchar

Nondeterministic

DATEPART

DATEPART ( datepart , date )

Returns an integer that represents the specified datepart of the specified date.

int

Nondeterministic

DAY

DAY ( date )

Returns an integer that represents the day day part of the specified date.

int

Deterministic

MONTH

MONTH ( date )

Returns an integer that represents the month part of a specified date.

int

Deterministic

YEAR

YEAR ( date )

Returns an integer that represents the year part of a specified date.

int

Deterministic

Function

Syntax

Return value

Return data type

Determinism

DATEFROMPARTS

DATEFROMPARTS ( year, month, day )

Returns a date value for the specified year, month, and day.

date

Deterministic

DATETIME2FROMPARTS

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Returns a datetime2 value for the specified date and time and with the specified precision.

datetime2 ( precision )

Deterministic

DATETIMEFROMPARTS

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Returns a datetime value for the specified date and time.

datetime

Deterministic

DATETIMEOFFSETFROMPARTS

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

datetime ( precision )

Deterministic

SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Returns a smalldatetime value for the specified date and time.

smalldatetime

Deterministic

TIMEFROMPARTS

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Returns a time value for the specified time and with the specified precision.

time ( precision )

Deterministic

Function

Syntax

Return value

Return data type

Determinism

DATEDIFF

DATEDIFF ( datepart , startdate , enddate )

Returns the number of date or time datepart boundaries that are crossed between two specified dates.

int

Deterministic

DATEDIFF_BIG

DATEDIFF_BIG ( datepart , startdate , enddate )

Returns the number of date or time datepart boundaries that are crossed between two specified dates.

bigint

Deterministic

Function

Syntax

Return value

Return data type

Determinism

DATEADD

DATEADD (datepart , number , date )

Returns a new datetime value by adding an interval to the specified datepart of the specified date.

The data type of the date argument

Deterministic

EOMONTH

EOMONTH ( start_date [, month_to_add ] )

Returns the last day of the month that contains the specified date, with an optional offset.

Return type is the type of start_date or date.

Deterministic

SWITCHOFFSET

SWITCHOFFSET (DATETIMEOFFSET , time_zone)

SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.

datetimeoffset with the fractional precision of the DATETIMEOFFSET

Deterministic

TODATETIMEOFFSET

TODATETIMEOFFSET (expression , time_zone)

TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

datetimeoffset with the fractional precision of the datetime argument

Deterministic

Function

Syntax

Return value

Return data type

Determinism

@@DATEFIRST

@@DATEFIRST

Returns the current value, for the session, of SET DATEFIRST.

tinyint

Nondeterministic

SET DATEFIRST

SET DATEFIRST { number | @number_var }

Sets the first day of the week to a number from 1 through 7.

Not applicable

Not applicable

SET DATEFORMAT

SET DATEFORMAT { format | @format_var }

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Not applicable

Not applicable

@@LANGUAGE

@@LANGUAGE

Returns the name of the language that is currently being used. @@LANGUAGE is not a date or time function. However, the language setting can affect the output of date functions.

Not applicable

Not applicable

SET LANGUAGE

SET LANGUAGE { [ N ] 'language' | @language_var }

Sets the language environment for the session and system messages. SET LANGUAGE is not a date or time function. However, the language setting affects the output of date functions.

Not applicable

Not applicable

sp_helplanguage

sp_helplanguage [ [ @language = ] 'language' ]

Returns information about date formats of all supported languages. sp_helplanguage is not a date or time stored procedure. However, the language setting affects the output of date functions.

Not applicable

Not applicable

Function

Syntax

Return value

Return data type

Determinism

ISDATE

ISDATE ( expression )

Determines whether a datetime or smalldatetime input expression is a valid date or time value.

int

ISDATE is deterministic only if you use it with the CONVERT function, when the CONVERT style parameter is specified, and when style is not equal to 0, 100, 9, or 109.

Topic

Description

CAST and CONVERT (Transact-SQL)

Provides information about the conversion of date and time values to and from string literals and other date and time formats.

Write International Transact-SQL Statements

Provides guidelines for portability of databases and database applications that use Transact-SQL statements from one language to another, or that support multiple languages.

ODBC Scalar Functions (Transact-SQL)

Provides information about ODBC scalar functions that can be used in Transact-SQL statements. This includes ODBC date and time functions.

AT TIME ZONE (Transact-SQL)

Provides time zone conversion.

© 2015 Microsoft
Export (0) Print
Expand All

@@DATEFIRST (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the current value, for a session, of SET DATEFIRST.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

@@DATEFIRST

SET DATEFIRST specifies the first day of the week. The U.S. English default is 7, Sunday.

This language setting affects the interpretation of character strings as they are converted to date values for storage in the database, and the display of date values that are stored in the database. This setting does not affect the storage format of date data. In the following example, the language is first set to Italian. The statement SELECT @@DATEFIRST; returns 1. The language is then set to us_english. The statement SELECT @@DATEFIRST; returns 7.

SET LANGUAGE Italian;
GO
SELECT @@DATEFIRST;
GO
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;

The following example sets the first day of the week to 5 (Friday), and assumes the current day, Today, to be Saturday. The SELECT statement returns the DATEFIRST value and the number of the current day of the week.

SET DATEFIRST 5;
SELECT @@DATEFIRST AS 'First Day'
    ,DATEPART(dw, SYSDATETIME()) AS 'Today';

Here is the result set.

First Day         Today
----------------  --------------
5                 2
© 2015 Microsoft
Export (0) Print
Expand All

CURRENT_TIMESTAMP (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

System_CAPS_noteNote

SYSDATETIME and SYSUTCDATE have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATE, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

This function is the ANSI SQL equivalent to GETDATE.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

CURRENT_TIMESTAMP

 

Takes no arguments.

datetime

Transact-SQL statements can refer to CURRENT_TIMESTAMP anywhere they can refer to a datetime expression.

CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed.

The following examples use the six SQL Server system functions that return current date and time to return the date, the time, or both. The values are returned in series so their fractional seconds might differ.

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();
/* Returned:
SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

/* Returned 
SYSDATETIME()      2007-05-03
SYSDATETIMEOFFSET()2007-05-03
SYSUTCDATETIME()   2007-05-04
CURRENT_TIMESTAMP  2007-05-03
GETDATE()          2007-05-03
GETUTCDATE()       2007-05-04
*/

SELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, SYSDATETIMEOFFSET())
    ,CONVERT (time, SYSUTCDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE())
    ,CONVERT (time, GETUTCDATE());

/* Returned
SYSDATETIME()      13:18:45.3490361
SYSDATETIMEOFFSET()13:18:45.3490361
SYSUTCDATETIME()   20:18:45.3490361
CURRENT_TIMESTAMP  13:18:45.3470000
GETDATE()          13:18:45.3470000
GETUTCDATE()       20:18:45.3470000
*/
© 2015 Microsoft
Export (0) Print
Expand All

DATEADD (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DATEADD (datepart , number , date )

datepart

Is the part of date to which an integer number is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw, w

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

number

Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.

If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime. To avoid ambiguity, use four-digit years. For information about two-digit years, see Configure the two digit year cutoff Server Configuration Option.

The return data type is the data type of the date argument, except for string literals.

The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.

dayofyear, day, and weekday return the same value.

Each datepart and its abbreviations return the same value.

If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:

SELECT DATEADD(month, 1, '2006-08-30');

SELECT DATEADD(month, 1, '2006-08-31');

The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. The following error message is returned: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."

SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');

The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is added to the date value exceeds the range of the date data type. The following error message is returned: "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow."

SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');

The seconds part of a smalldatetime value is always 00. If date is smalldatetime, the following apply:

  • If datepart is second and number is between -30 and +29, no addition is performed.

  • If datepart is second and number is less than-30 or more than +29, addition is performed beginning at one minute.

  • If datepart is millisecond and number is between -30001 and +29998, no addition is performed.

  • If datepart is millisecond and number is less than -30001 or more than +29998, addition is performed beginning at one minute.

DATEADD can be used in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

Addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime is not allowed.

Milliseconds have a scale of 3 (.123), microseconds have a scale of 6 (.123456), And nanoseconds have a scale of 9 (.123456789). The time, datetime2, and datetimeoffset data types have a maximum scale of 7 (.1234567). If datepart is nanosecond, number must be 100 before the fractional seconds of date increase. A number between 1 and 49 is rounded down to 0 and a number from 50 to 99 is rounded up to 100.

The following statements add a datepart of millisecond, microsecond, or nanosecond.

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';
SELECT '1 millisecond', DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)
UNION ALL
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)
UNION ALL
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);

Here is the result set.

1 millisecond     2007-01-01 13:10:10.1121111
2 milliseconds    2007-01-01 13:10:10.1131111
1 microsecond     2007-01-01 13:10:10.1111121
2 microseconds    2007-01-01 13:10:10.1111131
49 nanoseconds    2007-01-01 13:10:10.1111111
50 nanoseconds    2007-01-01 13:10:10.1111112
150 nanoseconds   2007-01-01 13:10:10.1111113

Addition is not allowed for time zone offset.

Each of the following statements increments datepart by an interval of 1.

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';
SELECT 'year', DATEADD(year,1,@datetime2)
UNION ALL
SELECT 'quarter',DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT 'month',DATEADD(month,1,@datetime2)
UNION ALL
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT 'day',DATEADD(day,1,@datetime2)
UNION ALL
SELECT 'week',DATEADD(week,1,@datetime2)
UNION ALL
SELECT 'weekday',DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT 'hour',DATEADD(hour,1,@datetime2)
UNION ALL
SELECT 'minute',DATEADD(minute,1,@datetime2)
UNION ALL
SELECT 'second',DATEADD(second,1,@datetime2)
UNION ALL
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2);

Here is the result set.

Year         2008-01-01 13:10:10.1111111
quarter      2007-04-01 13:10:10.1111111
month        2007-02-01 13:10:10.1111111
dayofyear    2007-01-02 13:10:10.1111111
day          2007-01-02 13:10:10.1111111
week         2007-01-08 13:10:10.1111111
weekday      2007-01-02 13:10:10.1111111
hour         2007-01-01 14:10:10.1111111
minute       2007-01-01 13:11:10.1111111
second       2007-01-01 13:10:11.1111111
millisecond  2007-01-01 13:10:10.1121111
microsecond  2007-01-01 13:10:10.1111121
nanosecond   2007-01-01 13:10:10.1111111

Each of the following statements increments datepart by a number large enough to also increment the next higher datepart of date.

DECLARE @datetime2 datetime2;
SET @datetime2 = '2007-01-01 01:01:01.1111111';
--Statement                                 Result   
------------------------------------------------------------------- 
SELECT DATEADD(quarter,4,@datetime2);     --2008-01-01 01:01:01.110
SELECT DATEADD(month,13,@datetime2);      --2008-02-01 01:01:01.110
SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.110
SELECT DATEADD(day,365,@datetime2);       --2008-01-01 01:01:01.110
SELECT DATEADD(week,5,@datetime2);        --2007-02-05 01:01:01.110
SELECT DATEADD(weekday,31,@datetime2);    --2007-02-01 01:01:01.110
SELECT DATEADD(hour,23,@datetime2);       --2007-01-02 00:01:01.110
SELECT DATEADD(minute,59,@datetime2);     --2007-01-01 02:00:01.110
SELECT DATEADD(second,59,@datetime2);     --2007-01-01 01:02:00.110
SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.110

The following examples use different types of expressions as arguments for the number and date parameters.

The following example adds 2 days to each value in the OrderDate column to derive a new column named PromisedShipDate.

USE AdventureWorks2012;
GO
SELECT SalesOrderID
    ,OrderDate 
    ,DATEADD(day,2,OrderDate) AS PromisedShipDate
FROM Sales.SalesOrderHeader;

Here is a partial result set.

SalesOrderID OrderDate               PromisedShipDate
------------ ----------------------- -----------------------
43659        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000
43660        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000
43661        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000
...
43702        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000
43703        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000
43704        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000
43705        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000
43706        2005-07-03 00:00:00.000 2005-07-05 00:00:00.000
...
43711        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000
43712        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000
...
43740        2005-07-11 00:00:00.000 2005-07-13 00:00:00.000
43741        2005-07-12 00:00:00.000 2005-07-14 00:00:00.000

The following example specifies user-defined variables as arguments for number and date.

DECLARE @days int = 365, 
        @datetime datetime = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */;
SELECT DATEADD(day, @days, @datetime);

Here is the result set.

-----------------------
2000-12-31 01:01:01.110

(1 row(s) affected)

The following example specifies SYSDATETIME for date.

SELECT DATEADD(month, 1, SYSDATETIME());

Here is the result set.

---------------------------
2013-02-06 14:29:59.6727944

(1 row(s) affected)

The following example uses scalar subqueries, MAX(ModifiedDate), as arguments for number and date. (SELECT TOP 1 BusinessEntityID FROM Person.Person) is an artificial argument for the number parameter to show how to select a number argument from a value list.

USE AdventureWorks2012;
GO
SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM Person.Person),
    (SELECT MAX(ModifiedDate) FROM Person.Person));

The following example uses a numeric expression (-(10/2)), unary operators (-), an arithmetic operator (/), and scalar system functions (SYSDATETIME) as arguments for number and date.

SELECT DATEADD(month,-(10/2), SYSDATETIME());

The following example uses a ranking function as arguments for number.

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,DATEADD(day,ROW_NUMBER() OVER (ORDER BY
        a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

The following example uses an aggregate window function as an argument for number.

USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,DATEADD(day,SUM(OrderQty) 
        OVER(PARTITION BY SalesOrderID),SYSDATETIME()) AS 'Total'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO
© 2015 Microsoft
Export (0) Print
Expand All

DATEDIFF (Transact-SQL)

 

Updated: December 2, 2015

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

For larger differences, see DATEDIFF_BIG (Transact-SQL). For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

DATEDIFF ( datepart , startdate , enddate )

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

startdate

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option.

enddate

See startdate.

  • Each datepart and its abbreviations return the same value.

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

If startdate and enddate are both assigned only a time value and the datepart is not a time datepart, 0 is returned.

A time zone offset component of startdate or endate is not used in calculating the return value.

Because smalldatetime is accurate only to the minute, when a smalldatetime value is used for startdate or enddate, seconds and milliseconds are always set to 0 in the return value.

If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values.

If startdate and enddate are of different date data types and one has more time parts or fractional seconds precision than the other, the missing parts of the other are set to 0.

The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. If different years are used for this example and if both startdate and endate are in the same calendar week, the return value for week would be 0.

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

DATEDIFF can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

The following examples use different types of expressions as arguments for the startdate and enddate parameters.

The following example calculates the number of day boundaries that are crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration
    (
    startDate datetime2
    ,endDate datetime2
    );
INSERT INTO dbo.Duration(startDate,endDate)
    VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT DATEDIFF(day,startDate,endDate) AS 'Duration'
FROM dbo.Duration;
-- Returns: 1

The following example uses user-defined variables as arguments for startdate and enddate.

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722'; 
SELECT DATEDIFF(day, @startdate, @enddate);

The following example uses scalar system functions as arguments for startdate and enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

The following example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

USE AdventureWorks2012;
GO
SELECT DATEDIFF(day,(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

The following example uses character constants as arguments for startdate and enddate.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635'
    , '2007-05-08 09:53:01.0376635');

The following example uses a numeric expression, (GETDATE ()+ 1), and scalar system functions, GETDATE and SYSDATETIME, as arguments for enddate.

USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE()+ 1) 
    AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day,1,SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO

The following example uses a ranking function as an argument for startdate.

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY 
        a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson s 
    INNER JOIN Person.Person p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

The following example uses an aggregate window function as an argument for startdate.

USE AdventureWorks2012;
GO
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty,soh.OrderDate
    ,DATEDIFF(day,MIN(soh.OrderDate) 
        OVER(PARTITION BY soh.SalesOrderID),SYSDATETIME() ) AS 'Total'
FROM Sales.SalesOrderDetail sod
    INNER JOIN Sales.SalesOrderHeader soh
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN(43659,58918);
GO
© 2015 Microsoft
Export (0) Print
Expand All

DATEDIFF_BIG (Transact-SQL)

 

Updated: December 1, 2015

Applies To: Azure SQL Database, SQL Data Warehouse, SQL Server 2016 Preview

Returns the count (signed big integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

DATEDIFF_BIG ( datepart , startdate , enddate )

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

startdate

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.

To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option.

enddate

See startdate.

Signed bigint

Returns count (signed bigint) of the specified datepart boundaries crossed between the specified startdate and enddate.

  • Each datepart and its abbreviations return the same value.

If the return value is out of range for bigint (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

If startdate and enddate are both assigned only a time value and the datepart is not a time datepart, 0 is returned.

A time zone offset component of startdate or endate is not used in calculating the return value.

Because smalldatetime is accurate only to the minute, when a smalldatetime value is used for startdate or enddate, seconds and milliseconds are always set to 0 in the return value.

If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values.

If startdate and enddate are of different date data types and one has more time parts or fractional seconds precision than the other, the missing parts of the other are set to 0.

The following statements have the same startdate and the same endate. Those dates are adjacent and differ in time by .0000001 second. The difference between the startdate and endate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1. If different years are used for this example and if both startdate and endate are in the same calendar week, the return value for week would be 0.

SELECT DATEDIFF_BIG(year, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(quarter, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(month, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(dayofyear, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(day, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(week, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(hour, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(minute, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(second, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999'

, '2006-01-01 00:00:00.0000000');

DATEDIFF_BIG can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function is deterministic.

The following examples use different types of expressions as arguments for the startdate and enddate parameters.

The following example calculates the number of day boundaries that are crossed between dates in two columns in a table.

CREATE TABLE dbo.Duration
    (
    startDate datetime2
    ,endDate datetime2
    );
INSERT INTO dbo.Duration(startDate,endDate)
    VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT DATEDIFF_BIG(day,startDate,endDate) AS 'Duration'
FROM dbo.Duration;
-- Returns: 1

For many additional examples, see the closely related examples in DATEDIFF (Transact-SQL).

© 2015 Microsoft
Export (0) Print
Expand All

DATEFROMPARTS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a date value for the specified year, month, and day.

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DATEFROMPARTS ( year, month, day )

year

Integer expression specifying a year.

month

Integer expression specifying a month, from 1 to 12.

day

Integer expression specifying a day.

DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned.

This function is capable of being remoted to SQL Server 2012 servers and above. It will not be remoted to servers with a version below SQL Server 2012.


The following example demonstrates the DATEFROMPARTS function.

SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;

Here is the result set.

Result
----------------------------------
2010-12-31

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DATENAME (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a character string that represents the specified datepart of the specified date 

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DATENAME ( datepart , date )

datepart

Is the part of the date to return. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw, w

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

TZoffset

tz

ISO_WEEK

ISOWK, ISOWW

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

To avoid ambiguity, use four-digit years. For information about two-digit years, see Configure the two digit year cutoff Server Configuration Option.

nvarchar

  • Each datepart and its abbreviations return the same value.

The return value depends on the language environment set by using SET LANGUAGE and by the Configure the default language Server Configuration Option of the login. The return value is dependant on SET DATEFORMAT if date is a string literal of some formats. SET DATEFORMAT does not affect the return value when the date is a column expression of a date or time data type.

When the date parameter has a date data type argument, the return value depends on the setting specified by using SET DATEFIRST.

If datepart argument is TZoffset (tz) and the date argument has no time zone offset, 0 is returned.

When date is smalldatetime, seconds are returned as 00.

If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned only when a literal is specified for date.

For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and returns 1900, January, 1, 1, Monday.

SELECT DATENAME(year, '12:10:30.123')
    ,DATENAME(month, '12:10:30.123')
    ,DATENAME(day, '12:10:30.123')
    ,DATENAME(dayofyear, '12:10:30.123')
    ,DATENAME(weekday, '12:10:30.123');

If date is specified as a variable or table column and the data type for that variable or column does not have the specified datepart, error 9810 is returned. The following code example fails because the date part year is not a valid for the time data type that is declared for the variable @t.

DECLARE @t time = '12:10:30.123'; 
SELECT DATENAME(year, @t); 

DATENAME can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

In SQL Server 2016, DATENAME implicitly casts string literals as a datetime2 type. This means that DATENAME does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

The following example returns the date parts for the specified date.

SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');

Here is the result set.

datepart

Return value

year, yyyy, yy

2007

quarter, qq, q

4

month, mm, m

October

dayofyear, dy, y

303

day, dd, d

30

week, wk, ww

44

weekday, dw

Tuesday

hour, hh

12

minute, n

15

second, ss, s

32

millisecond, ms

123

microsecond, mcs

123456

nanosecond, ns

123456700

TZoffset, tz

310

ISO_WEEK, ISOWK, ISOWW

44

© 2015 Microsoft
Export (0) Print
Expand All

DATEPART (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns an integer that represents the specified datepart of the specified date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DATEPART ( datepart , date )

datepart

Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

microsecond

mcs

nanosecond

ns

TZoffset

tz

ISO_WEEK

isowk, isoww

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.

To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option.

Each datepart and its abbreviations return the same value.

The return value depends on the language environment set by using SET LANGUAGE and by the Configure the default language Server Configuration Option of the login. If date is a string literal for some formats, the return value depends on the format specified by using SET DATEFORMAT. SET DATEFORMAT does not affect the return value when the date is a column expression of a date or time data type.

The following table lists all datepart arguments with corresponding return values for the statement SELECT DATEPART(datepart,'2007-10-30 12:15:32.1234567 +05:10'). The data type of the date argument is datetimeoffset(7). The nanosecond datepart return value has a scale of 9 (.123456700) and the last two positions are always 00.

datepart

Return value

year, yyyy, yy

2007

quarter, qq, q

4

month, mm, m

10

dayofyear, dy, y

303

day, dd, d

30

week, wk, ww

45

weekday, dw

1

hour, hh

12

minute, n

15

second, ss, s

32

millisecond, ms

123

microsecond, mcs

123456

nanosecond, ns

123456700

TZoffset, tz

310

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST.

January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The following table lists the return value for week and weekday datepart for '2007-04-21 ' for each SET DATEFIRST argument. January 1 is a Monday in the year 2007. April 21 is a Saturday in the year 2007. SET DATEFIRST 7, Sunday, is the default for U.S. English.

SET DATEFIRST

argument

week

returned

weekday

returned

1

16

6

2

17

5

3

17

4

4

17

3

5

17

2

6

17

1

7

16

7

The values that are returned for DATEPART (year, date), DATEPART (month, date), and DATEPART (day, date) are the same as those returned by the functions YEAR, MONTH, and DAY, f respectively.

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004. The highest week number in a year might be 52 or 53. This style of numbering is typically used in European countries/regions, but rare elsewhere.

The numbering system in different countries/regions might not comply with the ISO standard. There are at least six possibilities as shown in the following table

First day of week

First week of year contains

Weeks assigned two times

Used by/in

Sunday

1 January,

First Saturday,

1–7 days of year

Yes

United States

Monday

1 January,

First Sunday,

1–7 days of year

Yes

Most of Europe and the United Kingdom

Monday

4 January,

First Thursday,

4–7 days of year

No

ISO 8601, Norway, and Sweden

Monday

7 January,

First Monday,

7 days of year

No

 

Wednesday

1 January,

First Tuesday,

1–7 days of year

Yes

 

Saturday

1 January,

First Friday,

1–7 days of year

Yes

 

The TZoffset (tz) is returned as the number of minutes (signed). The following statement returns a time zone offset of 310 minutes.

SELECT DATEPART (TZoffset, 2007-05-10  00:00:01.1234567 +05:10);

If the datepart argument is TZoffset (tz) and the date argument is not of datetimeoffset data type, NULL is returned.

When date is smalldatetime, seconds are returned as 00.

If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned only when a literal is specified for date.

For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and returns 1900, 1, 1, 1, 2.

SELECT DATEPART(year, '12:10:30.123')
    ,DATEPART(month, '12:10:30.123')
    ,DATEPART(day, '12:10:30.123')
    ,DATEPART(dayofyear, '12:10:30.123')
    ,DATEPART(weekday, '12:10:30.123');

If date is specified as a variable or table column and the data type for that variable or column does not have the specified datepart, error 9810 is returned. The following code example fails because the date part year is not a valid for the time data type that is declared for the variable @t.

DECLARE @t time = '12:10:30.123'; 
SELECT DATEPART(year, @t); 

Fractional seconds are returned as shown in the following statements:

SELECT DATEPART(millisecond, '00:00:01.1234567'); -- Returns 123
SELECT DATEPART(microsecond, '00:00:01.1234567'); -- Returns 123456
SELECT DATEPART(nanosecond,  '00:00:01.1234567'); -- Returns 123456700

DATEPART can be used in the select list, WHERE, HAVING, GROUP BY and ORDER BY clauses.

In SQL Server 2016, DATEPART implicitly casts string literals as a datetime2 type. This means that DATEPART does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

The following example returns the base year. The base year is useful for date calculations. In the example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900. 

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0);
-- Returns: 1900    1    1 */
© 2015 Microsoft
Export (0) Print
Expand All

DATETIME2FROMPARTS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetime2 value for the specified date and time and with the specified precision.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

year

Integer expression specifying a year.

month

Integer expression specifying a month.

day

Integer expression specifying a day.

hour

Integer expression specifying hours.

minute

Integer expression specifying minutes.

seconds

Integer expression specifying seconds.

fractions

Integer expression specifying fractions.

precision

Integer literal specifying the precision of the datetime2 value to be returned.

datetime2 ( precision )

DATETIME2FROMPARTS returns a fully initialized datetime2 value. If the arguments are not valid, an error is raised. If required arguments are null, then null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

This function is capable of being remoted to SQL Server 2016 servers and above. It will not be remoted to servers that have a version below SQL Server 2016.

SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;

Here is the result set.

Result
---------------------------
2010-12-31 23:59:59.0000000

(1 row(s) affected)

The following example demonstrates the use of the fractions and precision parameters:

  1. When fractions has a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

  2. When fractions has a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

  3. When fractions has a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 5, 1 );
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 50, 2 );
SELECT DATETIME2FROMPARTS ( 2011, 8, 15, 14, 23, 44, 500, 3 );
GO

Here is the result set.

----------------------
2011-08-15 14:23:44.5

(1 row(s) affected)


----------------------
2011-08-15 14:23:44.50

(1 row(s) affected)


----------------------
2011-08-15 14:23:44.500

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DATETIMEFROMPARTS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetime value for the specified date and time.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

year

Integer expression specifying a year.

month

Integer expression specifying a month.

day

Integer expression specifying a day.

hour

Integer expression specifying hours.

minute

Integer expression specifying minutes.

seconds

Integer expression specifying seconds.

milliseconds

Integer expression specifying milliseconds.

datetime

DATETIMEFROMPARTS returns a fully initialized datetime value. If the arguments are not valid, then an error is raised. If required arguments are null, then a null is returned.

This function is capable of being remoted to SQL Server 2016 servers and above. It will not be remoted to servers that have a version below SQL Server 2016.


SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS Result;

Here is the result set.

Result
---------------------------
2010-12-31 23:59:59.000

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DATETIMEOFFSETFROMPARTS (Transact-SQL)

 

Updated: December 2, 2015

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

year

Integer expression specifying a year.

month

Integer expression specifying a month.

day

Integer expression specifying a day.

hour

Integer expression specifying hours.

minute

Integer expression specifying minutes.

seconds

Integer expression specifying seconds.

fractions

Integer expression specifying fractions.

hour_offset

Integer expression specifying the hour portion of the time zone offset.

minute_offset

Integer expression specifying the minute portion of the time zone offset.

precision

Integer literal specifying the precision of the datetimeoffset value to be returned.

datetimeoffset ( precision )

DATETIMEOFFSETFROMPARTS returns a fully initialized datetimeoffset data type. The offset arguments are used to represent the time zone offset. If the offset arguments are omitted, then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative. If minute_offset is specified without hour_offset, an error is raised. If other arguments are not valid, then an error is raised. If required arguments are null, then a null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

This function is capable of being remoted to SQL Server 2016 servers and above. It will not be remoted to servers that have a version below SQL Server 2016.

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;

Here is the result set.

Result
-------------------------------------------
2010-12-07 00:00:00.0000000 +00:00

(1 row(s) affected)

The following example demonstrates the use of the fractions and precision parameters:

  1. When fractions has a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

  2. When fractions has a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

  3. When fractions has a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 5, 12, 30, 1 );
SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 50, 12, 30, 2 );
SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 500, 12, 30, 3 );
GO

Here is the result set.

----------------------------------
2011-08-15 14:30:00.5 +12:30

(1 row(s) affected)


----------------------------------
2011-08-15 14:30:00.50 +12:30

(1 row(s) affected)


----------------------------------
2011-08-15 14:30:00.500 +12:30

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

DAY (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns an integer representing the day (day of the month) of the specified date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

DAY ( date )

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.

DAY returns the same value as DATEPART (day, date).

If date contains only a time part, the return value is 1, the base day.

The following statement returns 30. This is the number of the day.

SELECT DAY('2007-04-30T01:01:01.1234567 -07:00');

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0);
© 2015 Microsoft
Export (0) Print
Expand All

EOMONTH (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the last day of the month that contains the specified date, with an optional offset.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

EOMONTH ( start_date [, month_to_add ] )

start_date

Date expression specifying the date for which to return the last day of the month.

month_to_add

Optional integer expression specifying the number of months to add to start_date.

If this argument is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised.

This function can be remoted to SQL Server 2012 servers and higher. It cannot be remoted to servers with a version lower than SQL Server 2012.

DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Here is the result set.

Result
------------
2011-12-31

(1 row(s) affected)

DECLARE @date VARCHAR(255) = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO

Here is the result set.

Result
------------
2011-12-31

(1 row(s) affected)

DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO

Here is the result set.

This Month
-----------------------
2011-12-31

(1 row(s) affected)

Next Month
-----------------------
2012-01-31

(1 row(s) affected)

Last Month
-----------------------
2011-11-30

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

GETDATE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

System_CAPS_noteNote

SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

GETDATE ( )

datetime

Transact-SQL statements can refer to GETDATE anywhere they can refer to a datetime expression.

GETDATE is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. In addition,  use the OPTION (RECOMPILE) query hint to force  the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates for GETDATE() and will produce a more efficient query plan.

DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00'); 
SELECT * FROM t  
WHERE c1 > @dt OPTION (RECOMPILE);

The following examples use the six SQL Server system functions that return current date and time to return the date, time, or both. The values are returned in series; therefore, their fractional seconds might be different.

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();

Here is the result set.

SYSDATETIME() 2007-04-30 13:10:02.0474381

SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00

SYSUTCDATETIME() 2007-04-30 20:10:02.0474381

CURRENT_TIMESTAMP 2007-04-30 13:10:02.047

GETDATE() 2007-04-30 13:10:02.047

GETUTCDATE() 2007-04-30 20:10:02.047

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

Here is the result set.

SYSDATETIME() 2007-05-03

SYSDATETIMEOFFSET() 2007-05-03

SYSUTCDATETIME() 2007-05-04

CURRENT_TIMESTAMP 2007-05-03

GETDATE() 2007-05-03

GETUTCDATE() 2007-05-04

SELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, SYSDATETIMEOFFSET())
    ,CONVERT (time, SYSUTCDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE())
    ,CONVERT (time, GETUTCDATE());

Here is the result set.

SYSDATETIME() 13:18:45.3490361

SYSDATETIMEOFFSET()13:18:45.3490361

SYSUTCDATETIME() 20:18:45.3490361

CURRENT_TIMESTAMP 13:18:45.3470000

GETDATE() 13:18:45.3470000

GETUTCDATE() 20:18:45.3470000

© 2015 Microsoft
Export (0) Print
Expand All

GETUTCDATE (Transact-SQL)

 

Updated: December 2, 2015

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.

System_CAPS_noteNote

SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

GETUTCDATE()

datetime

Transact-SQL statements can refer to GETUTCDATE anywhere they can refer to a datetime expression.

GETUTCDATE is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

The following examples use the six SQL Server system functions that return current date and time to return the date, time or both. The values are returned in series; therefore, their fractional seconds might be different.

SELECT 'SYSDATETIME()      ', SYSDATETIME();
SELECT 'SYSDATETIMEOFFSET()', SYSDATETIMEOFFSET();
SELECT 'SYSUTCDATETIME()   ', SYSUTCDATETIME();
SELECT 'CURRENT_TIMESTAMP  ', CURRENT_TIMESTAMP;
SELECT 'GETDATE()          ', GETDATE();
SELECT 'GETUTCDATE()       ', GETUTCDATE();
/* Returned:
SYSDATETIME()            2007-05-03 18:34:11.9351421
SYSDATETIMEOFFSET()      2007-05-03 18:34:11.9351421 -07:00
SYSUTCDATETIME()         2007-05-04 01:34:11.9351421
CURRENT_TIMESTAMP        2007-05-03 18:34:11.933
GETDATE()                2007-05-03 18:34:11.933
GETUTCDATE()             2007-05-04 01:34:11.933
*/

SELECT 'SYSDATETIME()      ', CONVERT (date, SYSDATETIME());
SELECT 'SYSDATETIMEOFFSET()', CONVERT (date, SYSDATETIMEOFFSET());
SELECT 'SYSUTCDATETIME()   ', CONVERT (date, SYSUTCDATETIME());
SELECT 'CURRENT_TIMESTAMP  ', CONVERT (date, CURRENT_TIMESTAMP);
SELECT 'GETDATE()          ', CONVERT (date, GETDATE());
SELECT 'GETUTCDATE()       ', CONVERT (date, GETUTCDATE());

/* Returned: 
SYSDATETIME()            2007-05-03
SYSDATETIMEOFFSET()      2007-05-03
SYSUTCDATETIME()         2007-05-04
CURRENT_TIMESTAMP        2007-05-03
GETDATE()                2007-05-03
GETUTCDATE()             2007-05-04
*/

SELECT 'SYSDATETIME()      ', CONVERT (time, SYSDATETIME());
SELECT 'SYSDATETIMEOFFSET()', CONVERT (time, SYSDATETIMEOFFSET());
SELECT 'SYSUTCDATETIME()   ', CONVERT (time, SYSUTCDATETIME());
SELECT 'CURRENT_TIMESTAMP  ', CONVERT (time, CURRENT_TIMESTAMP);
SELECT 'GETDATE()          ', CONVERT (time, GETDATE());
SELECT 'GETUTCDATE()       ', CONVERT (time, GETUTCDATE());
/* Returned
SYSDATETIME()            18:25:01.6958841
SYSDATETIMEOFFSET()      18:25:01.6958841
SYSUTCDATETIME()         01:25:01.6958841
CURRENT_TIMESTAMP        18:25:01.6930000
GETDATE()                18:25:01.6930000
GETUTCDATE()             01:25:01.6930000
*/
© 2015 Microsoft
Export (0) Print
Expand All

ISDATE (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

ISDATE returns 0 if the expression is a datetime2 value.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). Note that the range for datetime data is 1753-01-01 through 9999-12-31, while the range for date data is 0001-01-01 through 9999-12-31.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

ISDATE ( expression )

expression

Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, are not allowed as the argument for ISDATE.

ISDATE is deterministic only if you use it with the CONVERT function, if the CONVERT style parameter is specified, and style is not equal to 0, 100, 9, or 109.

The return value of ISDATE depends on the settings set by SET DATEFORMAT, SET LANGUAGE and Configure the default language Server Configuration Option.

For examples of valid formats for which ISDATE will return 1, see the section "Supported String Literal Formats for datetime" in the datetime and smalldatetime topics. For additional examples, also see the Input/Output column of the "Arguments" section of CAST and CONVERT.

The following table summarizes input expression formats that are not valid and that return 0 or an error.

ISDATE expression

ISDATE return value

NULL

0

Values of data types listed in Data Types in any data type category other than character strings, Unicode character strings, or date and time.

0

Values of text, ntext, or image data types.

0

Any value that has a seconds precision scale greater than 3, (.0000 through .0000000...n). ISDATE will return 0 if the expression is a datetime2 value, but will return 1 if the expression is a valid datetime value.

0

Any value that mixes a valid date with an invalid value, for example 1995-10-1a.

0

The following example shows you how to use ISDATE to test whether a character string is a valid datetime.

IF ISDATE('2009-05-12 10:19:41.177') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';

The following statements show the values that are returned as a result of the settings of SET DATEFORMAT and SET LANGUAGE.

/* Use these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
/* Expression in mdy dateformat */
SELECT ISDATE('04/15/2008'); --Returns 1.
/* Expression in mdy dateformat */
SELECT ISDATE('04-15-2008'); --Returns 1. 
/* Expression in mdy dateformat */
SELECT ISDATE('04.15.2008'); --Returns 1. 
/* Expression in myd  dateformat */
SELECT ISDATE('04/2008/15'); --Returns 1.

SET DATEFORMAT mdy;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET DATEFORMAT mdy;
SELECT ISDATE('2008/04/15'); --Returns 1.

SET DATEFORMAT dmy;
SELECT ISDATE('15/04/2008'); --Returns 1.
SET DATEFORMAT dym;
SELECT ISDATE('15/2008/04'); --Returns 1.
SET DATEFORMAT ydm;
SELECT ISDATE('2008/15/04'); --Returns 1.
SET DATEFORMAT ymd;
SELECT ISDATE('2008/04/15'); --Returns 1.

SET LANGUAGE English;
SELECT ISDATE('15/04/2008'); --Returns 0.
SET LANGUAGE Hungarian;
SELECT ISDATE('15/2008/04'); --Returns 0.
SET LANGUAGE Swedish;
SELECT ISDATE('2008/15/04'); --Returns 0.
SET LANGUAGE Italian;
SELECT ISDATE('2008/04/15'); --Returns 1.

/* Return to these sessions settings. */
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
© 2015 Microsoft
Export (0) Print
Expand All

MONTH (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns an integer that represents the month of the specified date.

For an overview of all Transact-SQL date and time data types and functions, seeDate and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

MONTH ( date )

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable, or string literal.

MONTH returns the same value as DATEPART (month, date).

If date contains only a time part, the return value is 1, the base month.

The following statement returns 4. This is the number of the month.

SELECT MONTH('2007-04-30T01:01:01.1234567 -07:00');

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0);
© 2015 Microsoft
Export (0) Print
Expand All

SMALLDATETIMEFROMPARTS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a smalldatetime value for the specified date and time.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

year

Integer expression specifying a year.

month

Integer expression specifying a month.

day

Integer expression specifying a day.

hour

Integer expression specifying hours.

minute

Integer expression specifying minutes.

smalldatetime

This functions acts like a constructor for a fully initialized smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned.

This function is capable of being remoted to SQL Server 2016 servers and above. It will not be remoted to servers that have a version below SQL Server 2016.


SELECT SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) AS Result

Here is the result set.

Result
---------------------------
2011-01-01 00:00:00

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

SWITCHOFFSET (Transact-SQL)

 

Updated: December 2, 2015

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Topic link icon Transact-SQL Syntax Conventions

SWITCHOFFSET ( DATETIMEOFFSET, time_zone ) 

DATETIMEOFFSET

Is an expression that can be resolved to a datetimeoffset(n) value.

time_zone

Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

Use SWITCHOFFSET to select a datetimeoffset value into a time zone offset that is different from the time zone offset that was originally stored. SWITCHOFFSET does not update the stored time_zone value.

SWITCHOFFSET can be used to update a datetimeoffset column.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan. For more information about the RECOMPILE query hint, see Query Hints (Transact-SQL).

DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00'); 
SELECT * FROM t  
WHERE c1 > @dt OPTION (RECOMPILE);

The following example uses SWITCHOFFSET to display a different time zone offset than the value stored in the database.

CREATE TABLE dbo.test 
    (
    ColDatetimeoffset datetimeoffset
    );
GO
INSERT INTO dbo.test 
VALUES ('1998-09-20 7:45:50.71345 -5:00');
GO
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00') 
FROM dbo.test;
GO
--Returns: 1998-09-20 04:45:50.7134500 -08:00
SELECT ColDatetimeoffset
FROM dbo.test;
--Returns: 1998-09-20 07:45:50.7134500 -05:00
© 2015 Microsoft
Export (0) Print
Expand All

SYSDATETIME (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

System_CAPS_noteNote

SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SYSDATETIME ( )

datetime2(7)

Transact-SQL statements can refer to SYSDATETIME anywhere they can refer to a datetime2(7) expression.

SYSDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

System_CAPS_noteNote

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

The following examples use the six SQL Server system functions that return current date and time to return the date, time or both. The values are returned in series; therefore, their fractional seconds might be different.

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();
/* Returned:
SYSDATETIME()      2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME()   2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP  2007-04-30 13:10:02.047
GETDATE()          2007-04-30 13:10:02.047
GETUTCDATE()       2007-04-30 20:10:02.047

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

/* All returned 2007-04-30 */

SELECT CONVERT (time, SYSDATETIME())
    ,CONVERT (time, SYSDATETIMEOFFSET())
    ,CONVERT (time, SYSUTCDATETIME())
    ,CONVERT (time, CURRENT_TIMESTAMP)
    ,CONVERT (time, GETDATE())
    ,CONVERT (time, GETUTCDATE());

/* Returned
SYSDATETIME()      13:18:45.3490361
SYSDATETIMEOFFSET()13:18:45.3490361
SYSUTCDATETIME()   20:18:45.3490361
CURRENT_TIMESTAMP  13:18:45.3470000
GETDATE()          13:18:45.3470000
GETUTCDATE()       20:18:45.3470000
*/
© 2015 Microsoft
Export (0) Print
Expand All

SYSDATETIMEOFFSET (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

SYSDATETIMEOFFSET ( )

datetimeoffset(7)

Transact-SQL statements can refer to SYSDATETIMEOFFSET anywhere they can refer to a datetimeoffset expression.

SYSDATETIMEOFFSET is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

System_CAPS_noteNote

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

The following examples use the six SQL Server system functions that return current date and time to return the date, time, or both. The values are returned in series; therefore, their fractional seconds might be different.

The following example shows the different formats that are returned by the date and time functions.

SELECT SYSDATETIME() AS SYSDATETIME
    ,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
    ,SYSUTCDATETIME() AS SYSUTCDATETIME
    ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
    ,GETDATE() AS GETDATE
    ,GETUTCDATE() AS GETUTCDATE;

Here is the result set.

SYSDATETIME() 2007-04-30 13:10:02.0474381

SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00

SYSUTCDATETIME() 2007-04-30 20:10:02.0474381

CURRENT_TIMESTAMP 2007-04-30 13:10:02.047

GETDATE() 2007-04-30 13:10:02.047

GETUTCDATE() 2007-04-30 20:10:02.047

The following example shows you how to convert date and time values to date.

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

Here is the result set.

2007-04-30

2007-04-30

2007-04-30

2007-04-30

2007-04-30

2007-04-30

The following example shows you how to convert date and time values to time.

SELECT CONVERT (time, SYSDATETIME()) AS SYSDATETIME()
    ,CONVERT (time, SYSDATETIMEOFFSET()) AS SYSDATETIMEOFFSET()
    ,CONVERT (time, SYSUTCDATETIME()) AS SYSUTCDATETIME()
    ,CONVERT (time, CURRENT_TIMESTAMP) AS CURRENT_TIMESTAMP
    ,CONVERT (time, GETDATE()) AS GETDATE()
    ,CONVERT (time, GETUTCDATE()) AS GETUTCDATE();

Here is the result set.

SYSDATETIME() 13:18:45.3490361

SYSDATETIMEOFFSET()13:18:45.3490361

SYSUTCDATETIME() 20:18:45.3490361

CURRENT_TIMESTAMP 13:18:45.3470000

GETDATE() 13:18:45.3470000

GETUTCDATE() 20:18:45.3470000

© 2015 Microsoft
Export (0) Print
Expand All

SYSUTCDATETIME (Transact-SQL)

 

Updated: December 1, 2015

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

System_CAPS_noteNote

SYSDATETIME and SYSUTCDATE have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATE, and SYSDATETIMEOFFSET can be assigned to a variable of any one of the date and time types.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions.

Topic link icon Transact-SQL Syntax Conventions

SYSUTCDATETIME ( )

datetime2

Transact-SQL statements can refer to SYSUTCDATETIME anywhere they can refer to a datetime2 expression.

SYSUTCDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.

System_CAPS_noteNote

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

The following examples use the six SQL Server system functions that return current date and time to return the date, time, or both. The values are returned in series; therefore, their fractional seconds might be different.

The following example shows the different formats that are returned by the date and time functions.

SELECT SYSDATETIME() AS SYSDATETIME
    ,SYSDATETIMEOFFSET() AS SYSDATETIMEOFFSET
    ,SYSUTCDATETIME() AS SYSUTCDATETIME
    ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
    ,GETDATE() AS GETDATE
    ,GETUTCDATE() AS GETUTCDATE;

Here is the result set.

SYSDATETIME() 2007-04-30 13:10:02.0474381

SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00

SYSUTCDATETIME() 2007-04-30 20:10:02.0474381

CURRENT_TIMESTAMP 2007-04-30 13:10:02.047

GETDATE() 2007-04-30 13:10:02.047

GETUTCDATE() 2007-04-30 20:10:02.047

The following example shows you how to convert date and time values to date.

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

Here is the result set.

2007-04-30

2007-04-30

2007-04-30

2007-04-30

2007-04-30

2007-04-30

The following example shows you how to convert date and time values to time.

DECLARE @DATETIME DATETIME = GetDate();

DECLARE @TIME TIME

SELECT @TIME = CONVERT(time, @DATETIME)

SELECT @TIME AS 'Time', @DATETIME AS 'Date Time'

Here is the result set.

Time Date Time

13:49:33.6330000 2009-04-22 13:49:33.633

© 2015 Microsoft
Export (0) Print
Expand All

TIMEFROMPARTS (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns a time value for the specified time and with the specified precision.

Topic link icon Transact-SQL Syntax Conventions

Applies to: SQL Server (SQL Server 2012 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

hour

Integer expression specifying hours.

minute

Integer expression specifying minutes.

seconds

Integer expression specifying seconds.

fractions

Integer expression specifying fractions.

precision

Integer literal specifying the precision of the time value to be returned.

time ( precision )

TIMEROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

This function can be remoted to SQL Server 2012 servers and higher. It cannot be remoted to servers that have a version lower thanSQL Server 2012.

SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result;

Here is the result set.

Result
--------------------
23:59:59.0000000

(1 row(s) affected)

The following example demonstrates the use of the fractions and precision parameters:

  1. When fractions has a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

  2. When fractions has a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

  3. When fractions has a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT TIMEFROMPARTS ( 14, 23, 44, 5, 1 );
SELECT TIMEFROMPARTS ( 14, 23, 44, 50, 2 );
SELECT TIMEFROMPARTS ( 14, 23, 44, 500, 3 );
GO

Here is the result set.

----------------
14:23:44.5

(1 row(s) affected)


----------------
14:23:44.50

(1 row(s) affected)


----------------
14:23:44.500

(1 row(s) affected)
© 2015 Microsoft
Export (0) Print
Expand All

TODATETIMEOFFSET (Transact-SQL)

 

Applies To: Azure SQL Data Warehouse, Azure SQL Database, SQL Server

Returns a datetimeoffset value that is translated from a datetime2 expression.

Topic link icon Transact-SQL Syntax Conventions

TODATETIMEOFFSET ( expression , time_zone )

expression

Is an expression that resolves to a datetime2 value.

System_CAPS_noteNote

The expression cannot be of type text, ntext, or image because these types cannot be implicitly converted to varchar or nvarchar.

time_zone

Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example ‘+13.00’. The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time_zone.

System_CAPS_noteNote

If expression is a character string, it must be in the format {+|-}TZH:THM.

datetimeoffset. The fractional precision is the same as the datetime argument.

The following example changes the zone offset of the current date and time to time zone -07:00.

DECLARE @todaysDateTime datetime2;
SET @todaysDateTime = GETDATE();
SELECT TODATETIMEOFFSET (@todaysDateTime, '-07:00');
-- RETURNS 2007-08-30 15:51:34.7030000 -07:00

The following example changes the current time zone to -120 minutes.

DECLARE @todaysDate datetime2;
SET @todaysDate = GETDATE();
SELECT TODATETIMEOFFSET (@todaysDate, -120);
-- RETURNS 2007-08-30 15:52:37.8770000 -02:00

The following example adds a 13-hour time zone offset to a date and time.

DECLARE @dateTime datetimeoffset(7)= '2007-08-28 18:00:30';
SELECT TODATETIMEOFFSET (@dateTime, '+13:00');
-- RETURNS 2007-08-28 18:00:30.0000000 +13:00
© 2015 Microsoft
Export (0) Print
Expand All

YEAR (Transact-SQL)

 

Applies To: SQL Server 2014, SQL Server 2016 Preview

Returns an integer that represents the year of the specified date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Topic link icon Transact-SQL Syntax Conventions

YEAR ( date )

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.

YEAR returns the same value as DATEPART (year, date).

If date only contains a time part, the return value is 1900, the base year.

The following statement returns 2007. This is the number of the year.

SELECT YEAR('2007-04-30T01:01:01.1234567-07:00');

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0);
© 2015 Microsoft